quick excel formula (complicated) problem 1

[westma]

Can anyone tell me why this formula will not work in excel? Do I have too many if statements--I can't figure it out! Every time I push ctrl+shift+enter it says the formula contains an error and then highlights INDIRECT (bold below). What does this mean?

Thanks,
Matt

=SUM(IF($E$4<>1,IF($B$6=0,IF($D$13:INDIRECT($J$39)>$Y3-11.25,IF
($D$13:INDIRECT($J$39)<=$Y3+11.25,LOOKUP($B$13:INDIRECT($J$37),
Power_Curves!$H$3:$H$674,INDIRECT($K$36):INDIRECT($K$37)))),
IF($E$13:INDIRECT($J$40)>$Y3-11.25,IF($E$13:INDIRECT($J$40)<=$Y3+11.25,
LOOKUP($B$13:INDIRECT($J$37),Power_Curves!$H$3:$H$674,
INDIRECT($K$36):INDIRECT($K$37))))),IF($B$13:INDIRECT($J$37)>=
Power_Curves!$E$65,IF($B$13:INDIRECT($J$37)<=Power_Curves!$E$66,
IF($B$6=0,IF($D$13:INDIRECT($J$39)>$Y3-11.25,IF($D$13:INDIRECT($J$39)<=
$Y3+11.25,LOOKUP($B$13:INDIRECT($J$37),Power_Curves!$H$3:$H$674,
INDIRECT($K$36):INDIRECT($K$37)))),IF($E$13:INDIRECT($J$40)>$Y3-11.25,
IF($E$13:INDIRECT($J$40)<=$Y3+11.25,LOOKUP($B$13:INDIRECT($J$37),
Power_Curves!$H$3:$H$674,INDIRECT($K$36):INDIRECT($K$37)))))))))/
($K$29*(60/$E$6))

 

[BenRowe]

Indirect is expecting text reference, try "J37" (including double quote marks), instead.

 

[westma]

Sorry, this INDIRECT is the one that is highlighted:

=SUM(IF($E$4<>1,IF($B$6=0,IF($D$13:INDIRECT($J$39)>$Y3-11.25,IF
($D$13:INDIRECT($J$39)<=$Y3+11.25,LOOKUP($B$13:INDIRECT($J$37),
Power_Curves!$H$3:$H$674,INDIRECT($K$36):INDIRECT($K$37)))),
IF($E$13:INDIRECT($J$40)>$Y3-11.25,IF($E$13:INDIRECT($J$40)<=$Y3+11.25,
LOOKUP($B$13:INDIRECT($J$37),Power_Curves!$H$3:$H$674,
INDIRECT($K$36):INDIRECT($K$37))))),IF($B$13:INDIRECT($J$37)>=
Power_Curves!$E$65,IF($B$13:INDIRECT($J$37)<=Power_Curves!$E$66,
IF($B$6=0,IF($D$13:INDIRECT($J$39)>$Y3-11.25,IF($D$13:INDIRECT($J$39)<=
$Y3+11.25,LOOKUP($B$13:INDIRECT($J$37),Power_Curves!$H$3:$H$674,
INDIRECT($K$36):INDIRECT($K$37)))),IF($E$13:INDIRECT($J$40)>$Y3-11.25,
IF($E$13:INDIRECT($J$40)<=$Y3+11.25,LOOKUP($B$13:INDIRECT($J$37),
Power_Curves!$H$3:$H$674,INDIRECT($K$36):INDIRECT($K$37)))))))))/
($K$29*(60/$E$6))

I tried "J37" in its place and nothing works. The formula goes bad right at this LOOKUP statement; if I cut it off before this, it works fine. Any other suggestions?

 

[bluedragon2]

I would suggest breaking each SUM argument out into a seperate cell and see if you are geting any problems.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[TonyJollans]

Hi westma,

It's a tricky formula to make proper sense of out of context but it is behaving as though it has too many levels of nesting (although if I count them it doesn't seem so).

Try setting up a Name with a value of:
[blue][tt] =LOOKUP($B$13:INDIRECT($J$37),Power_Curves!$H$3:$H$674,INDIRECT($K$36):INDIRECT($K$37)[/tt][/blue]

and using the name instead of the LOOKUP formula.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[westma]

Hi, I am feeling much better thanks to you guys; thank you for the replies. TonyJollans, your suggestion worked wonders, and I was able to simplify things even farther. That was one thing I had never thought of before. Its nice starting out a day learning something new. Thanks again!

Matt