Rounding 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. I have managed to work out that where a cell is being divided by the cell at D5 the answer needs to be rounded to two decimal places before being timesed by 7 (this is because I am trying to replicate a process that was previously done manually. 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),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​

 

[LxQ]

I got lost trying to follow the logic... but try this: any time you have something like
((E5-F5)/D5)*7
use
ROUND((E5-F5)/D5,2)*7

also, instead of:
IF((E5>M5)*AND(M5>F5),

use:
IF(AND(E5>M5,M5>F5),

or just:
IF((E5>M5)*(M5>F5),

 

[Chris Waller]

I got lost trying to follow the logic... but try this: any time you have something like
((E5-F5)/D5)*7
use
ROUND((E5-F5)/D5,2)*7

also, instead of:
IF((E5>M5)*AND(M5>F5),

use:
IF(AND(E5>M5,M5>F5),

or just:
IF((E5>M5)*(M5>F5),

Thanks for the help. I have used your round part of the formula plus the If(andE5>M5,M5>F5 option, which appears to have worked. The only problem now is trying to replicate the answers that I have at work from a different system.

It is a bit worrying as most of the figures agree, but there are about 5 that differ by 1 to 5 pence. Perhaps, the other system does not calculate the same way that Excel does.

Thanks once again for your help it is very much appreciated.

 

[Chris Waller]

In the second part of the formula below, is it possible to roundup all the parts of the formula where it is doing a divide and times? I have tried several variations and all without success. Please see bold type below.


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))))))))

 

[miss_ell]

Hello,

Just a suggestion but instead of =ROUND((E5-F5)/D5,2)*7 why not try =ROUND(((E5-F5)/D5)*7,2)? The might help to reduce the difference in pennies.

 

[Chris Waller]

Thanks for posting your earlier idea. I will give it a go. I have just found out the other system in question has been rounding the figures incorrectly i.e. it has been rounding up in all instances.