Quick help with a simple function!

  • Thread starter Thread starter bobbly_bob
  • Start date Start date
B

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)))))))))
 
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.
 
They've exceeded the nested function limit of 7.

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

Biff
 
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
 
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
 
Back
Top