Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
Hi,
I have an equation I'm trying to put in excel 2003. It is currently: =IF(G3<0,"u.i less than zero",IF(Sheet2!$A2=TRUE,Sheet1!$G3*Sheet1!$E$2,IF (Sheet2!$B2=TRUE,Sheet1!$G3*Sheet1!$E$3,IF(Sheet2! $C2=TRUE,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$D2=TRUE ,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$E2=TRUE,Sheet1! $G3*Sheet1!$E$5,IF(Sheet2!$F2=TRUE,Sheet1!$G3*Shee t1!$E$6,IF(Sheet2!$G2=TRUE,Sheet1!$G3*Sheet1!$E$7, "unknown")))))))) The problem is that I need to include about 3 or 4 more IF statements similar to the ones already in the equation. When I include one more if statement I get an error that says there is a mistake in my formula when I hit "OK" it highlights the latest "IF" as if there were some problem with it. I can detect no problem. I have noticed that in the last IF statement excel no-longer assigns a color to the cell showing which cell you are referring to. Could someone please tell me if I'm exceeding the max limit. Or any other suggestions to get around this problem. Related topic: I did have all the work in the main formula rather than having the formula sub calculations occuring in sheet two. I ran into the same problem. I had hoped that splitting the sub calculations out of the original formula would help (it didn't) Thanks in advance, BudW |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
http://www.j-walk.com/ss/excel/usertips/tip080.htm
-- Kind regards, Niek Otten Microsoft MVP - Excel "BudW" wrote in message ... | Hi, | | I have an equation I'm trying to put in excel 2003. It is currently: | | =IF(G3<0,"u.i less than | zero",IF(Sheet2!$A2=TRUE,Sheet1!$G3*Sheet1!$E$2,IF (Sheet2!$B2=TRUE,Sheet1!$G3*Sheet1!$E$3,IF(Sheet2! $C2=TRUE,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$D2=TRUE ,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$E2=TRUE,Sheet1! $G3*Sheet1!$E$5,IF(Sheet2!$F2=TRUE,Sheet1!$G3*Shee t1!$E$6,IF(Sheet2!$G2=TRUE,Sheet1!$G3*Sheet1!$E$7, "unknown")))))))) | | The problem is that I need to include about 3 or 4 more IF statements | similar to the ones already in the equation. When I include one more if | statement I get an error that says there is a mistake in my formula when I | hit "OK" it highlights the latest "IF" as if there were some problem with it. | I can detect no problem. | | I have noticed that in the last IF statement excel no-longer assigns a color | to the cell showing which cell you are referring to. | | Could someone please tell me if I'm exceeding the max limit. Or any other | suggestions to get around this problem. | | Related topic: I did have all the work in the main formula rather than | having the formula sub calculations occuring in sheet two. I ran into the | same problem. I had hoped that splitting the sub calculations out of the | original formula would help (it didn't) | | Thanks in advance, | BudW |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
excel will only accept up to 7 nested if statements
I would think about a vlookup() or similar method for example you could have something like in a4 =--(Sheet2!A2) in B4 =Sheet1!G3*Sheet1!E2 in A5 =--(Sheet2!b2) IN b5 =Sheet1!G3*Sheet1!E3 ETC then the equation could be =if(G3<0,"u.i less than zero",vllookup(1,A4:B20,2) "BudW" wrote: Hi, I have an equation I'm trying to put in excel 2003. It is currently: =IF(G3<0,"u.i less than zero",IF(Sheet2!$A2=TRUE,Sheet1!$G3*Sheet1!$E$2,IF (Sheet2!$B2=TRUE,Sheet1!$G3*Sheet1!$E$3,IF(Sheet2! $C2=TRUE,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$D2=TRUE ,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$E2=TRUE,Sheet1! $G3*Sheet1!$E$5,IF(Sheet2!$F2=TRUE,Sheet1!$G3*Shee t1!$E$6,IF(Sheet2!$G2=TRUE,Sheet1!$G3*Sheet1!$E$7, "unknown")))))))) The problem is that I need to include about 3 or 4 more IF statements similar to the ones already in the equation. When I include one more if statement I get an error that says there is a mistake in my formula when I hit "OK" it highlights the latest "IF" as if there were some problem with it. I can detect no problem. I have noticed that in the last IF statement excel no-longer assigns a color to the cell showing which cell you are referring to. Could someone please tell me if I'm exceeding the max limit. Or any other suggestions to get around this problem. Related topic: I did have all the work in the main formula rather than having the formula sub calculations occuring in sheet two. I ran into the same problem. I had hoped that splitting the sub calculations out of the original formula would help (it didn't) Thanks in advance, BudW |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
In Excel there is a limit of 7 to the number of if statements you can include
in a single formula... You can check out this link if you want a way around it but you might be better off to rethink your solution... http://www.cpearson.com/excel/nested.htm -- HTH... Jim Thomlinson "BudW" wrote: Hi, I have an equation I'm trying to put in excel 2003. It is currently: =IF(G3<0,"u.i less than zero",IF(Sheet2!$A2=TRUE,Sheet1!$G3*Sheet1!$E$2,IF (Sheet2!$B2=TRUE,Sheet1!$G3*Sheet1!$E$3,IF(Sheet2! $C2=TRUE,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$D2=TRUE ,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$E2=TRUE,Sheet1! $G3*Sheet1!$E$5,IF(Sheet2!$F2=TRUE,Sheet1!$G3*Shee t1!$E$6,IF(Sheet2!$G2=TRUE,Sheet1!$G3*Sheet1!$E$7, "unknown")))))))) The problem is that I need to include about 3 or 4 more IF statements similar to the ones already in the equation. When I include one more if statement I get an error that says there is a mistake in my formula when I hit "OK" it highlights the latest "IF" as if there were some problem with it. I can detect no problem. I have noticed that in the last IF statement excel no-longer assigns a color to the cell showing which cell you are referring to. Could someone please tell me if I'm exceeding the max limit. Or any other suggestions to get around this problem. Related topic: I did have all the work in the main formula rather than having the formula sub calculations occuring in sheet two. I ran into the same problem. I had hoped that splitting the sub calculations out of the original formula would help (it didn't) Thanks in advance, BudW |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
<In Excel there is a limit of 7 to the number of if statements you can include in a single formula...
That is not entirely true; you can have many more. But you can only *nest* functions 7 levels deep (IF and other functions) in Excel 2003 and older. In Excel 2007 you can nest far deeper. -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim Thomlinson" wrote in message ... | In Excel there is a limit of 7 to the number of if statements you can include | in a single formula... You can check out this link if you want a way around | it but you might be better off to rethink your solution... | | http://www.cpearson.com/excel/nested.htm | -- | HTH... | | Jim Thomlinson | | | "BudW" wrote: | | Hi, | | I have an equation I'm trying to put in excel 2003. It is currently: | | =IF(G3<0,"u.i less than | zero",IF(Sheet2!$A2=TRUE,Sheet1!$G3*Sheet1!$E$2,IF (Sheet2!$B2=TRUE,Sheet1!$G3*Sheet1!$E$3,IF(Sheet2! $C2=TRUE,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$D2=TRUE ,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$E2=TRUE,Sheet1! $G3*Sheet1!$E$5,IF(Sheet2!$F2=TRUE,Sheet1!$G3*Shee t1!$E$6,IF(Sheet2!$G2=TRUE,Sheet1!$G3*Sheet1!$E$7, "unknown")))))))) | | The problem is that I need to include about 3 or 4 more IF statements | similar to the ones already in the equation. When I include one more if | statement I get an error that says there is a mistake in my formula when I | hit "OK" it highlights the latest "IF" as if there were some problem with it. | I can detect no problem. | | I have noticed that in the last IF statement excel no-longer assigns a color | to the cell showing which cell you are referring to. | | Could someone please tell me if I'm exceeding the max limit. Or any other | suggestions to get around this problem. | | Related topic: I did have all the work in the main formula rather than | having the formula sub calculations occuring in sheet two. I ran into the | same problem. I had hoped that splitting the sub calculations out of the | original formula would help (it didn't) | | Thanks in advance, | BudW |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
Is there a way for you to put in "sub-answers"? Make a section give an
answer in a cell then use that cell in another group of formulas that puts the answer in another cell, etc., thus daisy-chaining the formula. Each section can be checked and modified much more easily that way. I've found that in long complex formulas it is almost impossible to check for errors in the formula. But if I break it up into sections, I can then tell which section appears to be wrong. It is also easier to make changes. These don't have to show as you can hide sections with HIDE or GROUP, if you don't want them seen. But I think it makes an easier program to work on. My 2 cents. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apparently exceeds max formula length?
Tro this array formula workaround (press ctrl+shift+enter to execute):
=IF(G3<0,"u.i less than zero",IF(OR(Sheet2!$A3:$G3=TRUE), INDEX(Sheet1!$E$2:$E$7,MATCH(TRUE,Sheet2!$A3:$G3,0 )),"Unknown")) On Jan 26, 8:12 pm, BudW wrote: Hi, I have an equation I'm trying to put in excel 2003. It is currently: =IF(G3<0,"u.i less than zero",IF(Sheet2!$A2=TRUE,Sheet1!$G3*Sheet1!$E$2,IF (Sheet2!$B2=TRUE,Sheet1!$G3*Sheet1!$E$3,IF(Sheet2! $C2=TRUE,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$D2=TRUE ,Sheet1!$G3*Sheet1!$E$4,IF(Sheet2!$E2=TRUE,Sheet1! $G3*Sheet1!$E$5,IF(Sheet2!$F2=TRUE,Sheet1!$G3*Shee t1!$E$6,IF(Sheet2!$G2=TRUE,Sheet1!$G3*Sheet1!$E$7, "unknown")))))))) The problem is that I need to include about 3 or 4 more IF statements similar to the ones already in the equation. When I include one more if statement I get an error that says there is a mistake in my formula when I hit "OK" it highlights the latest "IF" as if there were some problem with it. I can detect no problem. I have noticed that in the last IF statement excel no-longer assigns a color to the cell showing which cell you are referring to. Could someone please tell me if I'm exceeding the max limit. Or any other suggestions to get around this problem. Related topic: I did have all the work in the main formula rather than having the formula sub calculations occuring in sheet two. I ran into the same problem. I had hoped that splitting the sub calculations out of the original formula would help (it didn't) Thanks in advance, BudW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Calculating a formula that exceeds 1,000 characters | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
formula to standarize cell length | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) |