Complicated Excel Formula

[Chris Waller]

I have inherited an Excel Spreadsheet (Excel 2002) at work which contains the following formula (it contains 381 characters). The calculations are mostly to do with money. Some of the answers I get are correct. Some are a penny out and others are several pence out. Does anyone know if this formula can be simplified, so that I can even start to understand it?</SPAN>

=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),M5-((F5/D5)*7),IF((R5>E5)*AND(E5>F5),((E5-F5)/D5)*7,IF((R5>F5)*AND(F5>E5),((E5-F5)/D5)*7,IF((F5>E5)*AND(E5>R5),0,IF((F5>(M5/7)*D5)*AND(M5>(E5/D5)*7),((E5/D5)*7)-M5,IF(M5="","",0))))))))

</SPAN>

TIA
Chris

 

[Jugantarji]

The formula is sound, as far as the syntax is concerned, can you explain in words what you are hoping to achieve?

 

[Jugantarji]

I'll explain the first few arguments and you can let me know if you understand.


If there is nothing in M5 the return a value of nothing
Otherwise IF E5 is bigger than M5 and M5 is bigger than F5 then the result is M5 minus F5
Otherwise If M5 is bigger than E5 and M5 is bigger than F5 then the result is the value of G5
Otherwise If F5 is bigger than E5 and E5 is bigger than M5 then the result is 0
Otherwise If M5 is bigger than F5 and F5 is bigger than E5 then the result is E5 minus F5
Otherwise If F5 is greater than M5 and M5 is greater than E5 then the result is E5 minus M5

Is this starting to make any sense?

 

[Chris Waller]

The formula is sound, as far as the syntax is concerned, can you explain in words what you are hoping to achieve?

FAO Jugantarji

I am trying to compare the Excel Spreadsheet with a completely different system at work. The problem is that the two systems i.e. Excel and the system at work are producing different results therefore I am uncertain which is correct. I thought that the Excel Spreadsheet formula may have been incorrect, but now I'm not sure.

 

[Chris Waller]

If you don't mind I will have a look at this post at work tomorrow and I will get back to you, if I manage to work it out. The problem is that I am comparing figures from Excel and a different system at work and I don't know what kind of formula is calculating the figures, so it may be a case of comparing apples and oranges. Thanks once again for your reply

 

[Jugantarji]

Okay come back if you need help.

 

[Chris Waller]

With your help I was able to prove that another system we use at work contained a fault. I found out on Friday that this is a known fault which was identified in 2011. I now have to trawl through some paperwork to find out what has gone on in the two interviening years and why this fault still persists. Thanks once again for your help.

 

[Jugantarji]

Glad I could help (sort of) and thanks for the feedback