Too Many Nested IF's??

[nickwood1066]

Hi,

I have the formula below in my spreadsheet (excel 2007) which works fine:

=IF(B47="Haywood",R47*0.1,IF(F47="Subcontractor",R47*0.1,IF(B47="City General",IF(R47<=25000,R47*0.05,IF(R47<=100000,R47*1.03+1250,R47*0.01+4250)),0)))

However I need to add another IF into the equation. IF F47="Project Co",R47*0

Is this possible or have I exceeded the Nested IF limit? I can't seem to get it to work and it says I have too many arguments.

In which case, is there a way around this, or another formula (without using VBA)?

Best regards

Nick

 

[Domski]

Try:

=IF(B47="Haywood",R47*0.1,IF(OR(F47="Subcontractor",F47="Project Co"),R47*0.1,IF(B47="City General",IF(R47<=25000,R47*0.05,IF(R47<=100000,R47*1.03+1250,R47*0.01+4250)),0)))

Dom

 

[doofusboy]

As far as I know, the limit for nested If statements is 7. It appears you are only adding a 6th, so you should be OK.

Do you really want to multiply by zero? IF F47="Project Co",R47*0
Why not just say IF F47="Project Co", 0 ?

 

[nickwood1066]

Thanks for the responses!

Thanks Domski, but this formula is multiplying the "Project Co" by 0.1, when it should be 0 (ZERO).

Well no I don't need to multiply by zero, but I need the outcome to be zero...

So what would the formula be, I can't seem to get it to give me anything than "There are too many arguments"...

Best regards

Nick

 

[Domski]

Sorry misread your post, try:

=IF(B47="Haywood",R47*0.1,IF(F47="Subcontractor",R47*0.1,IF(F47="Project Co",R47*0,IF(B47="City General",IF(R47<=25000,R47*0.05,IF(R47<=100000,R47*1.03+1250,R47*0.01+4250)),0))))

Dom

 

[nickwood1066]

Thank you Domski!

Works perfectly! Don't know why it wouldn't let me do it when I tried - I was obviously doing something wrong!

Much appreciated

Best regards

Nick