help with Nested if

[richa.pisces]

hi All,

I am a new member to this forum, I am currently using following formula where I have following conditions:

=IF(AND(BQ2="3A",BT2="Offshore",(CB2-CA2)>2000),CB2-CA2,(IF(AND(BQ2="3A",BT2="Offshore",(CB2-CA2)>=0),2000,IF(AND(BQ2="4B",BT2="Offshore",(CB2-CA2)>0,BZ2="A",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="4B",BT2="Offshore",(CB2-CA2)>0,BZ2="B",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="4A",BT2="Offshore",(CB2-CA2)>0,BZ2="A",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="4A",BT2="Offshore",(CB2-CA2)>0,BZ2="B",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="5B",BT2="Offshore",(CB2-CA2)>0,BZ2="A",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="5B",BT2="Offshore",(CB2-CA2)>0,BZ2="B",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="5A",BT2="Offshore",(CB2-CA2)>0,BZ2="A",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(AND(BQ2="5A",BT2="Offshore",(CB2-CA2)>0,BZ2="B",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,0)))))))))))

with this formula I am getting error that I have entered too many arguments for this function. I know formula is very big but not sure where I am making error and how I can make it short.

[Sc0tt1e]

For a start these are the same, greater than 0 & greater than 2000 (same thing). Plus aren't you only allowed a certain number of conditions (7 or 9 I think) does this exceed that limit (count the IF's).

Please Login or Register  to view this content.

This is mid formula and has a true and a false result, I would guess this is your problem.

Please Login or Register  to view this content.

I gave up after this, I think that's enough to get you going

[richa.pisces]

I in first error that you pointed out, I wanted to say do treatment only if value is greater then or equal to 0 and not if it is negative.

[FDibbins]

richa, welcome to the forum

I think that the way I would handle this would be to 1st, use a helper to combine BQ2 and BT2, although you are testing for BT2="Offshore" in every test, so you could eliminate that from the all teh AND()'s you have and put it in once at the start.

You would then start with...
=IF(BT2="Offshore",IF(

As for the rest, it's entirely possible that I am missing something, but these look identical, except for the test for A or B?
AND(BQ2="4B",(CB2-CA2)>0,BZ2="A",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(
AND(BQ2="4B",(CB2-CA2)>0,BZ2="B",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(

What exactly are you trying to do here, can you provide a sample workbook?

[sktneer]

What I have observed roughly from your formula is this.....

In nested IFs, the inner IF begins within the FALSE of the outer IF statement. So when a normal IF statement would be like this.....

=IF(logical_test,"Do this if logical test is found True","else do this if logical test is false")

And with a nested if it would be like this......

=IF(logical_test,"Do this if logical test is found True",IF(logical_test,"Do this if logical test is found True","else do this if logical test is false"))

Now in your nested if formula, you have IF statements like this.....

=IF(AND(BQ2="4B",(CB2-CA2)>0,BZ2="A",(CB2-CA2)>CA2*7%),CB2-CA2,CA*7%,IF(

Here blue is the logical test, green is for True and brown is for False, so your IF statement is completed and there is no room or scope for another nested IF statement.

Enter your formula in the formula cell, hit enter and after getting an error of too many arguments click ok and now click in the formula bar in the beginning of your formula and with the help of right arrow key go through the formula and notice the formula syntax pop up help to know where exactly you are in the formula. That will help you to correct the formula.

[richa.pisces]

Sample.xlsx hi ford please find attached sample file

[FDibbins]

Looking at your WB, I take it this is an abbreviated version of your original file? Your formula refers to BT2, but your table only goes as far as AJ?
Also, your references to columns like CB2-CA2 and BQ2, make it almost impossible for me to try and figure out what you are trying to do here

Can you either fix the references in your formula, to relate to your workbook, or walk me though what you are trying to do?