Too Many Nested IF's??

nickwood1066

Board Regular
Joined
Aug 21, 2009
Messages
55
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
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

Board Regular
Joined
Aug 21, 2009
Messages
55
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Board Regular
Joined
Aug 21, 2009
Messages
55
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top