Minimum of category

[agibsonsw]

Hello. Excel 2007.
I have a column containing letters d and f with numbers in the next column:
name number
d 1
f 2
d 3
f 4
d 5
f 6
Is there a formula that will tell me the minimum number for d? Thanks, Andy.

[HansV]

Let's say the names are in A2:A7 and the numbers in B2:B7.
In another cell, enter the following array formula (confirm with Ctrl+Shift+Enter):

=MIN(IF(A2:A7="d",B2:B7))

[agibsonsw]

I almost had it.. I put a 0 argument for the value if false - so the minimum was always zero?! Thanks, Andy.

[HansV]

It's easiest to omit the value-if-false part, but you could use 9.99999999999999E+307 (the highest value you can enter in Excel):

=MIN(IF(A2:A7="d",B2:B7,9.99999999999999E+307))

still as an array formula.

[agibsonsw]

Hi.
Is it possible to pick up the 2nd or 3rd smallest from the "d" category? Andy.

[HansV]

To get the 2nd smallest:

=SMALL(IF(A2:A7="d",B2:B7),2)

and for the 3rd smallest:

=SMALL(IF(A2:A7="d",B2:B7),3)

As in the previous examples, these are array formulas (confirm with Ctrl+Shift+Enter).

[agibsonsw]

You're a star :)