# Rounding Formula

#### Chris Waller

##### Board Regular
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

##### Well-known Member
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

IF((E5>M5)*AND(M5>F5),

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

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

#### Chris Waller

##### Board Regular
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

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

##### Board Regular
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

##### Active Member
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

##### Board Regular
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.

1,081,690
Messages
5,360,614
Members
400,592
Latest member

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...