# Nested IF formula - too many functions, but only 3

#### Tim4freedom

##### New Member
I am struggling with a formula with a 3 x nested IF statements. The error is "Too many arguments" when there are only 3.
I might have a bracket wrong, but doesn't appear to be.

Formula is: IF(AND(I6=0,\$CW\$6>\$b6),\$CS6,IF(AND(I6=0,\$CW\$6<\$b6),\$CQ6,IF(\$CW\$6>\$b6,((H6*\$CS6)+(I6*\$CT6))/(H6+I6)),((H6*\$CQ6)+(I6*\$CR6))/(H6+H6)))

Thanks

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
If statments can only have 2 Arguments, 1 for True, 1 for false.
Try:
=IF(AND(I6=0,\$CW\$6>\$B6),\$CS6,IF(AND(I6=0,\$CW\$6<\$B6),\$CQ6,IF(\$CW\$6>\$B6,((H6*\$CS6)+(I6*\$CT6))/(H6+I6))))*((H6*\$CQ6)+(I6*\$CR6))/(H6+H6)

If statments can only have 2 Arguments, 1 for True, 1 for false.
Try:
=IF(AND(I6=0,\$CW\$6>\$B6),\$CS6,IF(AND(I6=0,\$CW\$6<\$B6),\$CQ6,IF(\$CW\$6>\$B6,((H6*\$CS6)+(I6*\$CT6))/(H6+I6))))*((H6*\$CQ6)+(I6*\$CR6))/(H6+H6)

I thought All IF statements (if singular not nested) have 3 items between 2 commas, but with nested there are 2, until the last one, which has the OTHERWISE, like in...
IF(this, then that, otherwise), so the last IF needs 3 arguments?
This formula is similar the mine with the last IF having 3 arguments...
=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))

If mine still doesn't follow this, then how would I write it? Your example has a multiply (*) in it, which I don't want. I want that calculation section to be the last argument or outcome option.

I want the formula to state, if it's not any of the other qualifying criteria prior, then calculate ((H6*CQ6)+I6*\$CR6))/(H6+I6)). How do I write the formula, with another IF statement in it somehow?

Last edited:
I'm not commenting on your overall formula or suggesting a particular correction, but you do have too many arguments in one of your IF() statements, as Warship has pointed out.

I have highlighted the outer part of that IF statement red, spread it out a bit and marked the 4 arguments that you have inside it different colours. I think you probably just have a parenthesis in the wrong place somewhere as the browny/yellowy section has an IF with only 2 arguments.

Code:
``=IF(AND(I6=0,\$CW\$6>\$B6),\$CS6, [COLOR="Red"][B]IF([/B][/COLOR]    [B][COLOR="Blue"]AND(I6=0,\$CW\$6<\$B6)[/COLOR][/B],     [B][COLOR="SeaGreen"]\$CQ6[/COLOR][/B],      [COLOR="DarkOrange"][B]IF(\$CW\$6>\$B6,((H6*\$CS6)+(I6*\$CT6))/(H6+I6))[/B][/COLOR],       [COLOR="Purple"]((H6*\$CQ6)+(I6*\$CR6))/(H6+H6)[/COLOR]    [COLOR="red"][B] )[/B][/COLOR]   ))``

Last edited:
I think you might have the parentheses slightly wrong.

Excel will allow this, and I think it does what you want but I'm not 100% sure:

=IF(AND(I6=0,\$CW\$6>\$B6),\$CS6,IF(AND(I6=0,\$CW\$6<\$B6),\$CQ6,IF(\$CW\$6>\$B6,((H6*\$CS6)+(I6*\$CT6))/(H6+I6),((H6*\$CQ6)+(I6*\$CR6))/(H6+H6))))

This, slightly shorter and perhaps slightly clearer, formula is allowed too:

=IF(I7=0,IF(\$CW\$6>\$B7,\$CS7,\$CQ7),IF(\$CW\$6>\$B7,((H7*\$CS7)+(I7*\$CT7))/(H7+I7),((H7*\$CQ7)+(I7*\$CR7))/(H7+H7)))

I think you might have the parentheses slightly wrong.

Excel will allow this, and I think it does what you want but I'm not 100% sure:

=IF(AND(I6=0,\$CW\$6>\$B6),\$CS6,IF(AND(I6=0,\$CW\$6<\$B6),\$CQ6,IF(\$CW\$6>\$B6,((H6*\$CS6)+(I6*\$CT6))/(H6+I6),((H6*\$CQ6)+(I6*\$CR6))/(H6+H6))))

This, slightly shorter and perhaps slightly clearer, formula is allowed too:

=IF(I7=0,IF(\$CW\$6>\$B7,\$CS7,\$CQ7),IF(\$CW\$6>\$B7,((H7*\$CS7)+(I7*\$CT7))/(H7+I7),((H7*\$CQ7)+(I7*\$CR7))/(H7+H7)))

Oops, not sure if the cell references are right in the 2nd formula - I'd copied it down to test it.

Oops, not sure if the cell references are right in the 2nd formula - I'd copied it down to test it.

That's ok. I corrected it!

Replies
6
Views
391
Replies
6
Views
945
Replies
5
Views
273
Replies
4
Views
575
Replies
5
Views
1K

1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

### 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.

### Which adblocker are you using?

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

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