Chris Waller
Board Regular
 Joined
 Jan 18, 2009
 Messages
 183
 Office Version

 365
 Platform

 Windows
I have inherited a formula from an excolleague 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),M5F5,IF((M5>E5)*AND(M5>F5),G5,IF((F5>E5)*AND(E5>M5),0,IF((M5>F5)*AND(F5>E5),E5F5,IF((F5>M5)*AND(M5>E5),E5M5,""&IF(M5="","",0)))))),IF((E5>R5)*AND(R5>F5),ROUNDUP(M5((F5/D5)*7),2),IF((R5>E5)*AND(E5>F5),ROUNDUP(((E5F5)/D5)*7,2),IF((R5>F5)*AND(F5>E5),ROUNDUP(((E5F5)/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))))))))
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),M5F5,IF((M5>E5)*AND(M5>F5),G5,IF((F5>E5)*AND(E5>M5),0,IF((M5>F5)*AND(F5>E5),E5F5,IF((F5>M5)*AND(M5>E5),E5M5,""&IF(M5="","",0)))))),IF((E5>R5)*AND(R5>F5),ROUNDUP(M5((F5/D5)*7),2),IF((R5>E5)*AND(E5>F5),ROUNDUP(((E5F5)/D5)*7,2),IF((R5>F5)*AND(F5>E5),ROUNDUP(((E5F5)/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))))))))