Minimum value based on a range

[Mick]

To all,

I have a problem getting the minimum values for some data. Probably easiest
to give a simple example so here we go.

Criteria Value
1 3
2 6
1 7
4 8
3 9
1 9

What I want to do is look at the values in column 1 (criteria) and based on
this pull the minimum value from column 2 (value). ie. If I wanted the
minimum for criteria = 1 it would return 3. Something like the SUMIF or
COUNTIF function would be great but it deosn't seem to exist. I do not want
to use pivot tables for this if possible.

Any help is greatly appreciated.

Mick.

 

[Juan Sanchez]

Mick

You can use a simple array formula:

=MIN(IF(A1:A6=1,B1:B6,""))

Since it is an array formula you need to commit with
CTRL+SHIFT+ENTER... this means that once you typed it on
the cell instead of just enter you do all three together.

You'll know if you did it right if braces appear
automatically arround the formula: {Formula}

Cheers
Juan

 

[Mick]

Worked a treat.

Thanks,
Mick.

Mick

You can use a simple array formula:

=MIN(IF(A1:A6=1,B1:B6,""))

Since it is an array formula you need to commit with
CTRL+SHIFT+ENTER... this means that once you typed it on
the cell instead of just enter you do all three together.

You'll know if you did it right if braces appear
automatically arround the formula: {Formula}

Cheers
Juan