14 Nested IF Statements

[Chris Waller]

I have inherited a formula from an ex-colleague that contains approximately 14 nested IF statements.

I have read somewhere today that you cannot use more than 7.

I am using Excel 2002 and I would be grateful if someone could tell me how I can adjust the formula to ensure that it works correctly. I do have access to a different system and when I compare the results, out of 18 sets of figures, 5 of them are incorrect, by 1 to 5 pence.

The only solution I can come up with is that the part of the formula that is supposed to be rounding, is not working correctly. Any help you can give would be greatly appreciated. Please find the formula below. TIA


=IF(M5="","",IF(C5>0,IF((E5>M5)*AND(M5>F5),M5-F5,IF((M5>E5)*AND(M5>F5),G5,IF((F5>E5)*AND(E5>M5),0,IF((M5>F5)*AND(F5>E5),E5-F5,IF((F5>M5)*AND(M5>E5),E5-M5,""&IF(M5="","",0)))))),IF((E5>R5)*AND(R5>F5),ROUNDUP(M5-((F5/D5)*7),2),IF((R5>E5)*AND(E5>F5),ROUNDUP(((E5-F5)/D5)*7,2),IF((R5>F5)*AND(F5>E5),ROUNDUP(((E5-F5)/D5)*7,2),IF((F5>E5)*AND(E5>R5),0,IF((F5>(M5/7)*D5)*AND(M5>(E5/D5)*7),ROUNDUP(((E5/D5)*7)-M5,2),IF(M5="","",0))))))))

 

[Jonmo1]

It is EXTREMELY Difficult to reverse engineer a formula.
Taking a formula that does not produce the result you want and trying to "fix it" is very difficult.
Especially if we don't know why it's not giving the correct result.

It's far easier to take existing data, give a description of what calculations you want calculated on that data, and formulate a NEW formula that DOES do what you want.

This statement here should help us figure this out.

I know what the end result should be, but not how to get there.

Exactly "HOW" do you know what the end result 'should' be??
If you don't know the "how to get there" part, how do you know the result is wrong?
In order to know something is wrong, you must therefore know what the correct answer is.

Are you doing some math in your head? What is that math?
If you can tell us the "How you know the correct result" part, then we can create a formula that follows that logic in your head.

 

[Jonmo1]

Besides, the formula you posted does not exeed the 7 nested if limit.
I CAN actually enter the formula you posted into a cell in XL2003
If it exceeded the 7, then XL2003 would not even allow it to be entered.

Just because a formula has multiple if's does not mean they are all nested..

=IF(cond1,If(cond2,If(cond3,true,false),If(cond4,true,false)),If(cond5,true,false))

That is not 5 nested IF's, it's only 3.

cond2 is nested in the TRUE of cond1. = 2 nested ifs
cond3 is nested in the TRUE of cond2. = 3 nested ifs
However, cond4 is NOT nested within cond3. It is nested in the FALSE part of cond2 = still only 3 nested ifs
And cond5 is NOT nested within cond4 3 or 2, it is in the FALSE part of cond1 = now down to only 2 nested ifs


Hope that helps.

 

[pjmorris]

I think I've investigated this as far as I'm able with the logic behind the calculation being explained.

However, having stripped out all the ROUNDUP statements in both the formula in cell R5 and and amazingly long equation in N5 I think I can say conclusively that your problem has nothing to do with the use of ROUNDUP I would suspect a rounding error but from the information provided its impossible to tell where since all source data we have to work with is already constrained to 2 decimal places. My advice would be to understand what it is you're calculating and then start from scratch. The current equation seems to be ripe for a complete rewrite anyway.

Regards

 

[Chris Waller]

I think I've investigated this as far as I'm able with the logic behind the calculation being explained.

However, having stripped out all the ROUNDUP statements in both the formula in cell R5 and and amazingly long equation in N5 I think I can say conclusively that your problem has nothing to do with the use of ROUNDUP I would suspect a rounding error but from the information provided its impossible to tell where since all source data we have to work with is already constrained to 2 decimal places. My advice would be to understand what it is you're calculating and then start from scratch. The current equation seems to be ripe for a complete rewrite anyway.

Regards

Peter,

Thanks for all your help I will seriously have to give it a go. I may have to come back at a later date. Thanks again.