formula help

[Dave Hunt]

Hi folks,

Extention of a question I asked yesterday (thanks Biff!).

I have two columns. Column 1 contains the letters A, B,
C, or D. Column 2 contains a numeric value (1 - 36). I am
trying to find the max (and min) of column 2 when the
value in column 1 is "A" (or one of the other letters).

Simple example:

col1 col2
A 2
D 36
A 18
C 20
A 24 << col1 = "A" and max value
B 8
A 0

I've tried =SumProduct(A1:A6="A")*(MAX(B1:B6) but this
is just giving me zero (false, I guess).

The answer should be 24 in this case. Any idea how to
modify or take a different approach to get the max value?

Any help is greatly appreciated. Thanks in advance. This
is a great forum.

Dave Hunt

 

[Leo Heuser]

Hi Dave

One way:

These two array formulae will do the job:

Max:

=MAX((A1:A7="a")*B1:B7)

Min:

=MIN(IF(A1:A7="a",B1:B7))

Each formulae must be entered with
<Shift><Ctrl><Enter> instead of <Enter>,
also if edited later. If done correctly, Excel
will display the formulae in the formula bar
enclosed in curly brackets { } Don't enter
these brackets yourself.

You cannot use
=MIN((A1:A7="a")*B1:B7))
because the minimum (for all *positive* values
in B1:B7), will always be 0 (the instances,
where the condition A1:A7="a" is false, a
zero will be transferred to the MIN-function.

 

[Dave Hunt]

Thanks Leo, that works great (though I admit I don't
fully understand the distinction between a formula
entered with just the Enter key vs. Ctrl-Shift-Enter...).

Thanks for your help and Happy New Year!
Dave

 

[Leo Heuser]

You're welcome, Dave, and a Happy New Year to you!

To get an introduction to array formulae,
try paying a visit to:

http://cpearson.com/excel/array.htm