How to get =MIN() given flags?

[Guest]

Hi all,

Here is a breeze question for you guys!

I want to get a minimum number in Column B from the numbers whose flag in
Column A shows "Y".

<Scenario 1>
A B
1 1
2 Y 5
3 Y 8
4 Y 12

The required function should show 5.

<Scenario 2>
A B
1 1
2 Y 5
3 8
4 Y 12

The required function should show 5.

What is the array function to do the above?

Thanks in advance.

Tetsuya Oguma

 

[Bob Phillips]

=MIN(IF(A1:A5="Y",B1:B5))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Pete_UK]

Assuming your data occupies A1:B4, enter this array* formula in C1:

=MIN(IF((A1:A4="Y"),B1:B4))

Adjust the ranges to suit.

*As this is an array formula, once you have typed it in (or
subsequently edit it) you need to use CTRL-SHIFT-ENTER (CSE) rather
than just ENTER. If you do this correctly then Excel will wrap the
formula in curly braces { } when viewed in the formula bar - you must
not type these yourself.

Hope this helps.

Pete

 

[Guest]

Hi Tetsuya ..are there any blank cells on column B without a number ?