Max with criteria

  • Thread starter Thread starter John Michl
  • Start date Start date
J

John Michl

I need to calculate the max of range but only for values in a row that has a
cell with a certain value. The sample below is limited. They actual matrix
is a 200 x 200 matrix.

Col. A Col.B
1 50%
1 25%
0 100%
0 10%
1 75%

1 = True, 0 = False so sumproduct (Col A, Col B)/sum(Col A) gives the
average of all values in Col B where Col A = 1. What could I do to
calculate the Max and Min? In this example, the Max would be 75% (not 100%
since Col A = False for the 100%).

Thanks for the help.

- John
 
Figured it out.

{=MAX((COLA=1)*(COLB))} entered as an array formula (ctrl-shift-enter) where
COLA and COLB are the ranges below the labels.

Important note: This didn't work for me at first because a the COLB range
contained a null value that had been entered as part of a formula in one of
the cells. The original formula to calculate the values in COLB was
something like IF(something is true, A1/B2, ""). So if the criteria was
false, enter nothing. Note there is no space between the quotes. I then
copied and pasted the values of the matrix to a new table. With this psuedo
null value, the array formula wouldn't work. When I cleared that cell by
hitting the delete key, all worked OK. Apparently, what I thought was a
null value wasn't really null even though there was no space in the
resulting cell. If that doesn't make sense, post a question and I'll
provide more detail.

- John
 
Thanks, Dan. I figured that out as well. So here is the million dollar
question, why would it work for MAX but not MIN or AVERAGE? I need to do
those too! <grin>

- John
 
Thank doesn't make sense to me since if, in your example, A2 = 0, it doesn't
meet the criteria and therefore should be included in the calculation (at
least the way I understand the calculation.) I'll have to give it some more
thought. Thanks for the help!

- John
 
Here's a slightly modified version that will

=(MIN(IF((A1:A5)=1,(A1:A5)*(B1:B5),"?")))
=(MAX(IF((A1:A5)=1,(A1:A5)*(B1:B5),"?")))
=(AVERAGE(IF((A1:A5)=1,(A1:A5)*(B1:B5),"?")))

Dan E
 
If you array enter into, e.g., D1:D5, the formula =A1:A5*B1:B5, you'll
see why; some of the values are 0, so that's the MIN if the rest are >0.

Alan Beban
 
You are on the right track with your explanation, but not quite there yet. If there is a formula
in column B like IF(something is true, A1/B2, ""), it doesn't matter whether you put a space
between the quotes or not. Both "" and " " are text, and you can't multiply a text cell by a
number (coerced from TRUE/FALSE to a number, that is), just as you can't multiply A1*B1 if B1
contains that formula and it is returning "".

Another array formula that will work is

=MAX(IF(A1:A100=1,B1:B100))

In this case, if column A contains 1, the value in column B is returned. That could be a number
anything except an error value, i.e. number, text, logical.

If A1 doesn't contain 1, the logical value FALSE is returned.

Since MAX ignores text and TRUE/FALSE, you will get the maximum of the numbers only. But it will
not work if there are formulas in column B and one of them is producing an error result.
 
There's no need to multiply here.

From John's example, I thought he wanted to do the average, min, or max of the data in column B
only, not the average, min, or max of the product of columns A and B.

Multiplying column A times column B will only work if the criterion is that column A contains
the number 1.

It won't work if, for example, the criterion is that column A equals 5, or is greater than 10,
or equals some text value.

In his original post, John said "column A equals some value". It's true that he used the value 1
in his example, but I'm not at all sure that's the real criterion.

Formulas similar to those below will work regardless of the actual criterion:

=MIN(IF(A1:A5="whatever",B1:B5))
=MAX(IF(A1:A5="whatever",B1:B5))
=AVERAGE(IF(A1:A5="whatever",B1:B5))

You don't need to specify a return value for the FALSE condition: without it, the default value
FALSE is returned, and AVERAGE, MIN, and MAX all ignore TRUE and FALSE.
 
Back
Top