MAX and IF Problem
 

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

MAX and IF Problem
 

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


"Steve M" wrote in message ...
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

MAX and IF Problem
 

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

Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Steve M" wrote in message ...
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

MAX and IF Problem
 

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. {}
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Steve M" wrote:

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

MAX and IF Problem
 

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

On Mar 6, 1:34*pm, "Steve M" wrote:
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

MAX and IF Problem
 

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.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Steve M" wrote in message
...
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

MAX and IF Problem
 

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

Adjust your range to suit



"Steve M" wrote:

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