MIN IF?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steven.gibbs@wanadoo.fr - 19 Sep 2007 07:46 GMT

I have a table with two columns:

Col_1 Col_2
XXX-YYY 123
XXX-YYY 120
YYY-QQQ 143

I want to find the minimum in col_2 for each entry in Col_1.

I have done it using a pivot table but would like to be more flexible.

Is there a way of nesting the MIN and an IF statement?

Steve

Reply to this Message

David Biddulph - 19 Sep 2007 08:34 GMT

=MIN(IF(A$1:A$3=A1,B$1:B$3)) as an *array formula*, and copy down.

Signature

David Biddulph

>I have a table with two columns:
>
[quoted text clipped - 10 lines]
>
> Steve

Reply to this Message

Mike H - 19 Sep 2007 08:40 GMT

It's not quite clear what you wnat but I assume for the data below for
XXX-YYY you would want to return 120 and for YYY-QQQ you would return 143. If
that's correct try:-

=MIN(IF(A1:A4="XXX-YYY",B1:B4))

Alter the ranges to suit. It's an array so enter with Ctrl+Shift+Enter

Mike

> I have a table with two columns:
>
[quoted text clipped - 10 lines]
>
> Steve

Reply to this Message

steven.gibbs@wanadoo.fr - 19 Sep 2007 10:09 GMT

Works a dream - Thanks!

Reply to this Message

Dave Peterson - 19 Sep 2007 13:24 GMT

One more if you want to avoid returning a minimum of 0 when there are no
matches.

=IF(COUNTIF(A1:A4,"xxx-yyy")=0,"No matches",MIN(IF(A1:A4="XXX-YYY",B1:B4)))

Still an array formula.

> I have a table with two columns:
>
[quoted text clipped - 10 lines]
>
> Steve

Signature

Dave Peterson

Reply to this Message