Nested IF formula - too many functions, but only 3

[Tim4freedom]

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.
Help please.

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

 

[Warship]

If statments can only have 2 Arguments, 1 for True, 1 for false.
Your 2nd "If" has three.
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)

 

[Tim4freedom]

If statments can only have 2 Arguments, 1 for True, 1 for false.
Your 2nd "If" has three.
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?

 

[Peter_SSs]

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.

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

 

[Norie]

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

 

[Tim4freedom]

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

Thanks everyone for your help and yes Norrie, your formula worked!

 

[Norie]

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

 

[Tim4freedom]

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!