Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
At the start of this formula, I want to add a 7th IF statement that checks
the condition of another cell, and sets this whole formula to "--" if the other cell is "". When I try to add it, I get a standard formula error message box, and then the last MAX is highlighted. What's going on? =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2),( AT2+AU2),(AZ2+BA2),(BF2+BG2)),(X2+Y2)=BQ2),V6,IF {and so on five more times, checking next AF+AG. The formula ends with: if false, "--". (I have left out the $ before each cell reference for brevity. The entire formula is about 700 to 900 characters long, depending how far down the sheet it is, but I can't add the 7th IF statement to even the shortest one.) What prompted the need for the 7th if statement is that without the underlying data cells being filled in, a #VALUE! error is reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to equal "" when the underlying data are absent. I have two other formulas very similar to this one, one checking just X,AF,AN, etc, and the other checking Y, AG,AO, etc. They don't report the error message. Only the formula with the combined X+Y, AF+AG, etc is a problem. There is also another combined X+Y, AF+AG, etc formula showing the same error message, but it only has 3 IF statments, and I am able to add the beginning 4th to cure the problem. Thanks in advance for any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
Hello:
You hit the limit of nested if's. From the Excel help file: Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on. Pieter Vandenberg manxman wrote: : At the start of this formula, I want to add a 7th IF statement that checks : the condition of another cell, and sets this whole formula to "--" if the : other cell is "". When I try to add it, I get a standard formula error : message box, and then the last MAX is highlighted. What's going on? : =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2),( AT2+AU2),(AZ2+BA2),(BF2+BG2)),(X2+Y2)=BQ2),V6,IF : {and so on five more times, checking next AF+AG. The formula ends with: if : false, "--". (I have left out the $ before each cell reference for brevity. : The entire formula is about 700 to 900 characters long, depending how far : down the sheet it is, but I can't add the 7th IF statement to even the : shortest one.) What prompted the need for the 7th if statement is : that without the underlying data cells being filled in, a #VALUE! error is : reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to : equal "" when the underlying data are absent. I have two other formulas very : similar to this one, one checking just X,AF,AN, etc, and the other checking : Y, AG,AO, etc. They don't report the error message. Only the formula with : the combined X+Y, AF+AG, etc is a problem. There is also another combined : X+Y, AF+AG, etc formula showing the same error message, but it only has 3 IF : statments, and I am able to add the beginning 4th to cure the problem. : Thanks in advance for any help : : |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
manxman wrote...
At the start of this formula, I want to add a 7th IF statement that checks the condition of another cell, and sets this whole formula to "--" if the other cell is "". When I try to add it, I get a standard formula error message box, and then the last MAX is highlighted. What's going on? =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2), (AT2+AU2),(AZ2+BA2), (BF2+BG2)),(X2+Y2)=BQ2),V6,IF {and so on five more times, checking next AF+AG. The formula ends with: if false, "--". (I have left out the $ before each cell reference for brevity. The entire formula is about 700 to 900 characters long, depending how far down the sheet it is, but I can't add the 7th IF statement to even the shortest one.) .... Don't worry about brevity. Post your entire actual working formulas as plain text. If you mean you're checking whether each of the X+Y, AF+AG, AN+AO, etc. values equals the maximum of all such values, you don't need multiple IFs. You could use a lookup. =INDEX(V6*{1;0;0;0;0;0}+AL6*{0;1;0;0;0;0}+...,MATC H(MAX(X2+Y2,AF2+AG2,...), (X2+Y2)*{1;0;0;0;0;0}+(AF2+AG2)*{0;1;0;0;0;0}+..., 0)) which can easily be modified to =IF(A1="","--",INDEX(...)) that without the underlying data cells being filled in, a #VALUE! error is reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to equal "" when the underlying data are absent. . . . .... If these cells could be "", then using them as arithmetic operands will generate #VALUE! errors. If they should be treated as zeros, why not set them to 0 rather than ""? Alternatively, wrap them inside N() calls, e.g., =IF(A1="","--",INDEX(N(V6)*{1;0;0;0;0;0}+N(AL6)*{0;1;0;0;0;0}+. .., MATCH(MAX(N(X2)+N(Y2),N(AF2)+N(AG2),...),(N(X2)+N( Y2))*{1;0;0;0;0;0} +(N(AF2)+N(AG2))*{0;1;0;0;0;0}+...,0)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
I'm aware of the seven level limit. The formula had six levels, and it won't
accept the seventh. That is what is frustrating me. "vandenberg p" wrote: Hello: You hit the limit of nested if's. From the Excel help file: Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on. Pieter Vandenberg manxman wrote: : At the start of this formula, I want to add a 7th IF statement that checks : the condition of another cell, and sets this whole formula to "--" if the : other cell is "". When I try to add it, I get a standard formula error : message box, and then the last MAX is highlighted. What's going on? : =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2),( AT2+AU2),(AZ2+BA2),(BF2+BG2)),(X2+Y2)=BQ2),V6,IF : {and so on five more times, checking next AF+AG. The formula ends with: if : false, "--". (I have left out the $ before each cell reference for brevity. : The entire formula is about 700 to 900 characters long, depending how far : down the sheet it is, but I can't add the 7th IF statement to even the : shortest one.) What prompted the need for the 7th if statement is : that without the underlying data cells being filled in, a #VALUE! error is : reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to : equal "" when the underlying data are absent. I have two other formulas very : similar to this one, one checking just X,AF,AN, etc, and the other checking : Y, AG,AO, etc. They don't report the error message. Only the formula with : the combined X+Y, AF+AG, etc is a problem. There is also another combined : X+Y, AF+AG, etc formula showing the same error message, but it only has 3 IF : statments, and I am able to add the beginning 4th to cure the problem. : Thanks in advance for any help : : |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
It's not necessarily IF, it's for all functions
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "manxman" wrote in message ... I'm aware of the seven level limit. The formula had six levels, and it won't accept the seventh. That is what is frustrating me. "vandenberg p" wrote: Hello: You hit the limit of nested if's. From the Excel help file: Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on. Pieter Vandenberg manxman wrote: : At the start of this formula, I want to add a 7th IF statement that checks : the condition of another cell, and sets this whole formula to "--" if the : other cell is "". When I try to add it, I get a standard formula error : message box, and then the last MAX is highlighted. What's going on? : =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2),( AT2+AU2),(AZ2+BA2),(BF2+BG2)),(X2+Y2)=BQ2),V6,IF : {and so on five more times, checking next AF+AG. The formula ends with: if : false, "--". (I have left out the $ before each cell reference for brevity. : The entire formula is about 700 to 900 characters long, depending how far : down the sheet it is, but I can't add the 7th IF statement to even the : shortest one.) What prompted the need for the 7th if statement is : that without the underlying data cells being filled in, a #VALUE! error is : reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to : equal "" when the underlying data are absent. I have two other formulas very : similar to this one, one checking just X,AF,AN, etc, and the other checking : Y, AG,AO, etc. They don't report the error message. Only the formula with : the combined X+Y, AF+AG, etc is a problem. There is also another combined : X+Y, AF+AG, etc formula showing the same error message, but it only has 3 IF : statments, and I am able to add the beginning 4th to cure the problem. : Thanks in advance for any help : : |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
"manxman" wrote in message
... "vandenberg p" wrote: manxman wrote: : At the start of this formula, I want to add a 7th IF statement that checks : the condition of another cell, and sets this whole formula to "--" if the : other cell is "". When I try to add it, I get a standard formula error : message box, and then the last MAX is highlighted. What's going on? : =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2),( AT2+AU2),(AZ2+BA2),(BF2+BG2)),(X2+Y2)=BQ2),V6,IF : {and so on five more times, checking next AF+AG. The formula ends with: if : false, "--". (I have left out the $ before each cell reference for brevity. : The entire formula is about 700 to 900 characters long, depending how far : down the sheet it is, but I can't add the 7th IF statement to even the : shortest one.) What prompted the need for the 7th if statement is : that without the underlying data cells being filled in, a #VALUE! error is : reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to : equal "" when the underlying data are absent. I have two other formulas very : similar to this one, one checking just X,AF,AN, etc, and the other checking : Y, AG,AO, etc. They don't report the error message. Only the formula with : the combined X+Y, AF+AG, etc is a problem. There is also another combined : X+Y, AF+AG, etc formula showing the same error message, but it only has 3 IF : statments, and I am able to add the beginning 4th to cure the problem. : Thanks in advance for any help You hit the limit of nested if's. From the Excel help file: Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on. I'm aware of the seven level limit. The formula had six levels, and it won't accept the seventh. That is what is frustrating me. It might be worth seeing what happens if you remove some of the other brackets which may not be needed, such as where you've got (X2+Y2) and(AF2+AG2), etc. Hopefully those expressions don't need brackets. -- David Biddulph |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
Here is the whole formula:
=IF(AND(($X2+$Y2)=MAX(($X2+$Y2),($AF2+$AG2),($AN2+ $AO2),($AT2+$AU2),($AZ2+$BA2),($BF2+$BG2)),($X2+$Y 2)=BQ$2),$V6,IF(AND(($AF2+$AG2)=MAX(($X2+$Y2),($A F2+$AG2),($AN2+$AO2),($AT2+$AU2),($AZ2+$BA2),($BF2 +$BG2)),($AF2+$AG2)=BQ$2),$AD6,IF(AND(($AN2+$AO2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)),($AN2+$AO2)=BQ$2),$AL6, IF(AND(($AT2+$AU2)=MAX(($X2+$Y2),($AF2+$AG2),($AN2 +$AO2),($AT2+$AU2),($AZ2+$BA2),($BF2+$BG2)),($AT2+ $AU2)=BQ$2),$AR6,IF(AND(($AZ2+$BA2)=MAX(($X2+$Y2) ,($AF2+$AG2),($AN2+$AO2),($AT2+$AU2),($AZ2+$BA2),( $BF2+$BG2)),($AZ2+$BA2)=BQ$2),$AX6,IF(AND(($BF2+$ BG2)=MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$ AU2),($AZ2+$BA2),($BF2+$BG2)),($BF2+$BG2)=BQ$2),$ BD6,"--")))))) "Harlan Grove" wrote: manxman wrote... At the start of this formula, I want to add a 7th IF statement that checks the condition of another cell, and sets this whole formula to "--" if the other cell is "". When I try to add it, I get a standard formula error message box, and then the last MAX is highlighted. What's going on? =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2), (AT2+AU2),(AZ2+BA2), (BF2+BG2)),(X2+Y2)=BQ2),V6,IF {and so on five more times, checking next AF+AG. The formula ends with: if false, "--". (I have left out the $ before each cell reference for brevity. The entire formula is about 700 to 900 characters long, depending how far down the sheet it is, but I can't add the 7th IF statement to even the shortest one.) .... Don't worry about brevity. Post your entire actual working formulas as plain text. If you mean you're checking whether each of the X+Y, AF+AG, AN+AO, etc. values equals the maximum of all such values, you don't need multiple IFs. You could use a lookup. =INDEX(V6*{1;0;0;0;0;0}+AL6*{0;1;0;0;0;0}+...,MATC H(MAX(X2+Y2,AF2+AG2,...), (X2+Y2)*{1;0;0;0;0;0}+(AF2+AG2)*{0;1;0;0;0;0}+..., 0)) which can easily be modified to =IF(A1="","--",INDEX(...)) that without the underlying data cells being filled in, a #VALUE! error is reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to equal "" when the underlying data are absent. . . . .... If these cells could be "", then using them as arithmetic operands will generate #VALUE! errors. If they should be treated as zeros, why not set them to 0 rather than ""? Alternatively, wrap them inside N() calls, e.g., =IF(A1="","--",INDEX(N(V6)*{1;0;0;0;0;0}+N(AL6)*{0;1;0;0;0;0}+. .., MATCH(MAX(N(X2)+N(Y2),N(AF2)+N(AG2),...),(N(X2)+N( Y2))*{1;0;0;0;0;0} +(N(AF2)+N(AG2))*{0;1;0;0;0;0}+...,0)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't add 7th IF statement to long formula.
manxman wrote...
Here is the whole formula: [reformatted] =IF(AND(($X2+$Y2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)), ($X2+$Y2)=BQ$2),$V6, IF(AND(($AF2+$AG2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)), ($AF2+$AG2)=BQ$2),$AD6, IF(AND(($AN2+$AO2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)), ($AN2+$AO2)=BQ$2),$AL6, IF(AND(($AT2+$AU2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)), ($AT2+$AU2)=BQ$2),$AR6, IF(AND(($AZ2+$BA2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)), ($AZ2+$BA2)=BQ$2),$AX6, IF(AND(($BF2+$BG2) =MAX(($X2+$Y2),($AF2+$AG2),($AN2+$AO2),($AT2+$AU2) ,($AZ2+$BA2),($BF2+$BG2)), ($BF2+$BG2)=BQ$2),$BD6, "--")))))) OK, so I did guess what you were trying to do: find the maximum sum of pairs of cells in nonadjacent ranges and return a corresponding value but also check that these cells sum to something greater than some other cell. Your ranges are uniformly spaced, every 6 columns, so take advantage of that. Try the array formula =IF(MAX(IF(MOD(COLUMN($V6:$BD6)-COLUMN($V6),6)=0,$X2:$BF2+$Y2:$BG2))= BQ$2,INDEX($V6:$BD6,MATCH(MAX($X2+$Y2,$AF2+$AG2,$A N2+$AO2, $AT2+$AU2,$AZ2+$BA2,$BF2+$BG2),IF(MOD(COLUMN($V6:$ BD6)-COLUMN($V6),6) =0,$X2:$BF2+$Y2:$BG2),0)),"--") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula too long | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |