MAX and IF Problem

  • Thread starter Thread starter Steve M
  • Start date Start date
S

Steve M

I attempted to resolve this function problem previously but worded things so
poorly that no one could help me. I will try again. This time I will try to
explain in words what I am trying to do.

I want to find the max of values in column B only if the if formula in the
same row in column L returns TRUE (there will be either TRUE, FALSE or a
blank in this cell)

Note that if the entry in column B is blank, the corresponding entry in
column L will also be blank. If column B contains a value, column L will
contain the result of an if formula-either TRUE or FALSE.

Note too that the results in cells in column B are not values, but the
results from formulas.

TIA
 
Steve,

The key to your problem is that the formula you were trying in response to your first post was an
array formula, which means that you must enter it using a three key combination Ctrl-Shift-Enter.

=MIN(IF(L2:L20,B2:B20))
=MAX(IF(L2:L20,B2:B20))

HTH,
Bernie
MS Excel MVP
 
I should have been more specific - the formula provided by Ron Rosenfeld is the one that should have
worked...

Bernie
MS Excel MVP
 
An array formula will do this:
=MAX(IF(L1:L4=TRUE,B1:B4))
L1-L4 has true, false or blank, B1-B4 has the numbers to find the max of.
You have to accept the formula by pressing ctl+shift then enter, you did it
right if you see braces around the formula in the bar. {}
 
You need something like this array* formula:

=MAX(IF(L1:L100,B1:B100))

I've assumed you have data in rows 1 to 100 - adjust to suit.

* As this is an array formula then you must commit it with CTRL-SHIFT-
ENTER (CSE) rather than the usual ENTER. Use CSE again if you amend
the formula. If done correctly, you will see curly braces { } around
the formula when viewed in the formula bar - don't type these
yourself.

Hope this helps.

Pete
 
Bernie's formula will work as long the assumptions are correct. However, in
your original post, you said you had *text* of TRUE or FALSE, not values of
TRUE or FALSE. If you actually have text, you will need:

=max(if(Ll2:L20="TRUE",B2:B20)
entered with Control-Shift-Enter

Also, your original formula had different range sizes (eg, L2:L17 and
B2:B20). This won't work. The number of cells in both ranges must be the
same.

Regards,
Fred.
 
Back
Top