Find the minimum numerical value in a list based on a criteria in Excel 2010

To find the minimum numerical value in a list, based on a criteria, we can use the MIN function along with the If function in Excel 2010.

MIN: This function is used to return the smallest number in a set of values. It ignores logical values and text.

Syntax of “MIN” function: =MIN(number1, [number2],….)

Example: Range A1:A4 contains a list of numbers from which we need to find the minimum number.

Follow the below given steps:-

  • Select the cell B1 and write the formula.
  • =MIN(A1:A4), press Enter on your keyboard.
  • The function will return 3.
  • 3 is the minimum value in the range A1:A4.

img1
IF: - Check whether a condition is met and returns one value if True and another value if False.

The syntax of “IF” function =if(logical test,[value_if_true],[value_if_false])

First the formula will do the logical test, and then return the output based on whether the logical test evaluates to true or false.

For example:Cells A2 and A3 contain the number 3 and 5. If the number is 3, the formula should return “Yes” else “No”.

=IF (A1=3,”Yes”,”No”)

img2

Let’s take an example to understand how we can find the minimum numerical value in a list based on certaincriteria.

We have data for certain items in range A2:D24. Column A contains items, Column B contains zone and column C contains sold unit.

img3

We need to find the minimum sold quantity zone wise for each item.

Follow the below given steps:-

  • Select the cell D2.
  • Write the formula to find out the minimum value
  • =MIN(IF($A$2:$A$31=A2,IF($B$2:$B$31=B2,$C$2:$C$31,””),””))
  • Press Ctrl+Shift+Enter.

img4

  • The function will return the minimum value in the east zone for handsets.
  • By using this formula, you can find out the minimum value for each zone item-wise.

Note: This is an array function, hence you cannot paste the formula in multiple cells. You will have to paste the formula one by one in each cell.

img5

This is the way we can find out the minimum number in the list based on certain criteria in Microsoft Excel 2010 and 2013.

 

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube