Quick help with a simple function!

[bobbly_bob]

Hi all, first time posting here.

I'm just working on a command for a sheet here at work and I think I
must have been staring at it for too long. The sequence below returns
an error and excel won't accept it as a formula. I think I'm missing an
outcome, but I can't see where,

cheers,

=IF(B9=0,0,(IF(C9="TPL",VLOOKUP(B9,Q7:Y32,9,FALSE),IF(B9=Q22,Z22,IF(B9=Q21,Z21,IF(B9=Q20,Z20,IF(H9="Y",IF(C9>50000,(C9-50000)*E9*F9*I9,0),IF(VLOOKUP(B9,Q7:W32,7,FALSE)>IF(C9>200000,IF(G9=1.1,(C9-200000)*E9*F9*G9*I9*0.75+200000*E9*F9*G9*I9,(C9-200000)*E9*F9*I9*0.75+200000*E9*F9*I9),IF(G9=1.1,C9*E9*F9*G9*I9,C9*E9*F9*I9)),VLOOKUP(B9,Q7:W32,7,FALSE),IF(C9>200000,IF(G9=1.1,(C9-200000)*E9*F9*G9*I9*0.75+200000*E9*F9*G9*I9,(C9-200000)*E9*F9*I9*0.75+200000*E9*F9*I9),IF(G9=1.1,C9*E9*F9*G9*I9,C9*E9*F9*I9)))))))))

 

[tim m]

Simple little function? lol
After a couple of read throughs by my count you have 23 left brackets and
only 22 right ones. Also I believe there is a character limit for formulas
as well, not sure what it is though.

 

[Biff]

They've exceeded the nested function limit of 7.

There's too much "stuff" going on in there to suggest anything.

Biff

 

[Greg Wilson]

I concur with Tim that you have 23 left parentheses and 22 right. You also
have more nesting levels than allowed at eight - max seven nesting levels.
The following is a brave attempt at fixing it. I have substituted this:

IF(NOT(ISNA(MATCH(B9, Q20:Q22, 0))), OFFSET(Z19, MATCH(B9, Q20:Q22, 0), 0)

For this:

If(B9=Q22, Z22, If(B9=Q21, Z21, If(B9=Q20, Z20…

thus reducing the nesting levels to six.

=IF(B9=0,0,IF(C9="TPL",VLOOKUP(B9,Q7:Y32,9,FALSE),IF(NOT(ISNA(MATCH(B9,
Q20:Q22, 0))), OFFSET(Z19, MATCH(B9, Q20:Q22, 0),
0),IF(H9="Y",IF(C9>50000,(C9-50000)*E9*F9*I9,0),IF(VLOOKUP(B9,Q7:W32,7,FALSE)>IF(C9>200000,IF(G9=1.1,(C9-200000)*E9*F9*G9*I9*0.75+200000*E9*F9*G9*I9,(C9-200000)*E9*F9*I9*0.75+200000*E9*F9*I9),IF(G9=1.1,C9*E9*F9*G9*I9,C9*E9*F9*I9)),VLOOKUP(B9,Q7:W32,7,FALSE),IF(C9>200000,IF(G9=1.1,(C9-200000)*E9*F9*G9*I9*0.75+200000*E9*F9*G9*I9,(C9-200000)*E9*F9*I9*0.75+200000*E9*F9*I9),IF(G9=1.1,C9*E9*F9*G9*I9,C9*E9*F9*I9)))))))

Hope it helps.

Regards,
Greg

 

[bobbly_bob]

Thats the ticket,
thanks for that guys, works like a charm now, I think you might be
hearing alot more from me in the future,
thanks again