Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |