Quick help with a simple function!

[email protected] · 07-19-2006, 11:20 PM

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 · 07-20-2006, 12:15 AM

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.

"[email protected]" wrote:

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

Biff · 07-20-2006, 12:45 AM

They've exceeded the nested function limit of 7.

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

Biff

"tim m" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "[email protected]" wrote:
>
>> 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)))))))))
>>
>>

Greg Wilson · 07-20-2006, 12:50 AM

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

"[email protected]" wrote:

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

[email protected] · 07-20-2006, 01:25 AM

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

Greg Wilson wrote:
> 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
>
> "[email protected]" wrote:
>
> > 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)))))))))
> >
> >