MAX and IF Problem

[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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

I should have been more specific - the formula provided by Ron Rosenfeld is the one that should have
worked...

Bernie
MS Excel MVP

 

[John Bundy]

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. {}

 

[Pete_UK]

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

 

[Fred Smith]

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.

 

[Teethless mama]

=MAX(INDEX((L1:L10=TRUE)*(B1:B10),0))

Adjust your range to suit