Minimum value if

How To Get Minimum Value If In Excel

Assuming that you have a set of data and you want to get minimum value based on criteria, then you can use the MIN function plus the IF function.

Generic Formula:

=MIN(IF(rng=criteria,values))

where rng is the item range.

Application Example

Let’s say that you have a set of data for workers (drivers), on the number of trips and hours taken, you can get a minimum value based on the criteria using MIN/IF functions. Check out the following example:

Figure 1. Example 1 of MIN/IF Function

From the example, here’s the formula in cell G7:

=MIN(IF(B6:B17=F7,D6:D17))

……where B6:B17 is the name range and D6:D17 is the time taken for trips. It is important to NOTE that the formula should be entered manually with ctrl+Shift+Enter.

How the MIN/IF Formula Works

From the inside, it is clear IF is the first function to be evaluated with B6:B17=F7 logic test. In this case, an array of TRUE and FALSE values is generated, where “TRUE” is corresponding to rows in which the driver name matches what’s in cell F7. The array from this logical test will also “filter” the time values. If the value for time is TRUE; it is returned by IF in the array but if the return is FALSE, it’s replaced by a FALSE or boolean FALSE.

 

Finally, the MIN function will return the minimum value which is 1.23 hrs for this example. The FALSE values are ignored.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar