Apparently exceeds max formula length?

[Guest]

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*Sheet1!$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

 

[Niek Otten]

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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*Sheet1!$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

 

[Guest]

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)

 

[Guest]

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

 

[Niek Otten]

<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

| 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*Sheet1!$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

 

[Guest]

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.

 

[Lori]

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"))