• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested if and argument limitation solution

S

Shibu John

New Member
Hi Team,

This is my first thread being a new member of the forum and I needed your assistance with the below mentioned formula

: =IF($D5="A",IF('Data Input Sheet'!$F5=""," ",IF('Data Input Sheet'!$F5=100,Parameters!$D$35,IF('Data Input Sheet'!$F5<=Parameters!$E$35,0,IF(AND('Data Input Sheet'!$F5>=Parameters!$B$35,'Data Input Sheet'!$F5<=Parameters!$C$35),((Parameters!$D$35)/(Parameters!$C$35-Parameters!$B$35))*('Data Input Sheet'!$F5-Parameters!$E$35))))),IF($D5="B",IF('Data Input Sheet'!$F5=""," ",IF('Data Input Sheet'!$F5=100,Parameters!$D$24,IF('Data Input Sheet'!$F5<=Parameters!$E$24,0,IF(AND('Data Input Sheet'!$F5>=Parameters!$B$24,'Data Input Sheet'!$F5<=Parameters!$C$24),((Parameters!$D$24)/(Parameters!$C$24-Parameters!$B$24))*('Data Input Sheet'!$F5-Parameters!$E$24)))))),IF($D5="C",IF('Data Input Sheet'!$F5=""," ",IF('Data Input Sheet'!$F5=100,Parameters!$D$24,IF('Data Input Sheet'!$F5<=Parameters!$E$24,0,IF(AND('Data Input Sheet'!$F5>=Parameters!$B$24,'Data Input Sheet'!$F5<=Parameters!$C$24),((Parameters!$D$24)/(Parameters!$C$24-Parameters!$B$24))*('Data Input Sheet'!$F5-Parameters!$E$24)))))))

When I add the IF($D5="C" string, I get an argument exceeded message thus the cell is not accepting the formula

What I am trying to do here is If cell $D5 has "A", "B" or "C" mentioned, the relevant conditions should be executed.

Could any one assist me in making sure I am able to run the above formula even if it means i modify the formula without changing the conditions.

Warm Regards,
Shibu John.
 
Hi Shibu!

As you probably understand, your nested IFs are quite impossible to understand to someone from outside of your head :)

From a brief look at your code, what I could tell is that you're using a nested structure of this type:
Code: 
=IF($D5="A",
    IF('Data Input Sheet'!$F5=""," ",Things_to_do_if_D5_is_A),
IF($D5="B",
    IF('Data Input Sheet'!$F5=""," ",Things_to_do_if_D5_is_B),
IF($D5="C",
    IF('Data Input Sheet'!$F5=""," ",Things_to_do_if_D5_is_C),Things_to_do_is_D5_is_neither_ABC))

ie, you're testing F5 to be "" 3 times when you could be doing it before testing D5 to be A B or C, like this:
Code: 
=IF('Data Input Sheet'!$F5=""," ",
IF($D5="A",Things_to_do_if_D5_is_A,
IF($D5="B",Things_to_do_if_D5_is_A,
IF($D5="C",Things_to_do_if_D5_is_A,Things_to_do_is_D5_is_neither_ABC)))

This way you would spare one nested IF inside each of the D5 test IF's...

Im sure you needed more help with this but if you're only 1 nested IF too many, rearranging the code could be the solution to your problem.

Best
Nuno
 
Thanks Nuno. I wanted to confirm on how does ecxel count the nested arguments in IF to 7.

Also if anyone also could add their comments on getting my formula to work
 
You must log in or register to reply here.
Back
Top