Solved

# rounding problems

Posted on 2001-07-27
Medium Priority
629 Views
Is there a good resolution for rounding double's.
The normal round function is not working properly
The function with int(x * 10^dec +0.5 etc is not always working properly when the last figure is 5
e.g. 23.345 gives 23.34
Maybe there is a toolbox for it?
0
Question by:dgb

LVL 11

Expert Comment

ID: 6326189
normally the function:

int(x * 10^dec + 0.5) / 10^dec

should work.

it would also return 23.35 in the example above.

23.345 * 100 = 2334.5
2334.5+0.5=2335
int(2335)=2335
2335/100=23.35
0

LVL 43

Expert Comment

ID: 6326203
dgb, the rounding function is working (at least the way it is designed it is).

http://support.microsoft.com/support/kb/articles/Q196/6/52.ASP

And

http://support.microsoft.com/support/kb/articles/Q194/9/83.ASP

Explain why and the first link provides functions which can be used in place of the standard round/fix functions to provide whichever form of rounding you prefer.
0

LVL 18

Expert Comment

ID: 6326205
'got this to work fine.

Option Explicit

Private Sub Command1_Click()

Dim k As Double

k = 23.345

Debug.Print Rounder(k, 2)

End Sub

Private Function Rounder(x As Double, n As Long) As Double

Dim v As Double

v = x * 10 ^ n + 0.5

Rounder = Int(v)
Rounder = Rounder / 10 ^ n

End Function

are you sure the variable doesn't actually hold 23.3449999..

that would round to 23.345 to 3 decimals, but 23.34 to 2 decimals

0

LVL 6

Expert Comment

ID: 6326206
Have you tried VB's in-built Round function?
e.g.
Round(x,dec)
0

LVL 18

Expert Comment

ID: 6326212
...but doubles are funny things, sometimes a value can't be held exactly & ends up as 23.3499999..instead of 23.35, then the rounding goes wrong.

dgb, I managed to repeat your error, but splitting the calculation into stages seems to have stopped it.

It really is odd, I got the answer 23.35, later it was 23.34 - now I can only get 23.35
0

LVL 10

Accepted Solution

caraf_g earned 400 total points
ID: 6326258
This is a well known problem with VB's rounding. The reason is that some bright spark in the VB development team decided that VB should use so-called "banker's rounding", which rounds 5 to the nearest *even* digit, so that

1.5 is rounded to 2
2.5 is rounded to 2
3.5 is rounded to 4
4.5 is rounded to 4

etc.

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=10113932

Thankfully, there is a solution. Use the format function

If you do this

MsgBox CDbl(Format(2.5, "0#"))
returns 3, as it should

MsgBox CDbl(Format(2.25, "0#.#"))
returns 2.3, as it should
0

LVL 10

Expert Comment

ID: 6326260
sorry. mistake

MsgBox CDbl(Format(2.5, "#0"))
returns 3, as it should

MsgBox CDbl(Format(2.25, "#0.0"))
returns 2.3, as it should
0

LVL 4

Expert Comment

ID: 6326266
Personally, I think it's a bug in the product that a .xxx5 round goes down ... In all roundings it goes up (invoices, ...)

What I do:
If the "rest" is exactly .5 (or .05 or the numbers you need), I manually add 0.01 and do the rounding.

This way, I have a nice rounding.
0

LVL 18

Expert Comment

ID: 6326271
caraf_g

I don't agree, in this case dbg is using 'the round .5 up' convention, via the int(x + .5) device.  (Unless he is using the format command somewhere in advance of his rounding)

24.5 would always round 'up' to 25.0 in that case

23.345 should always go to 23.35 with the gode given.

Unbelievably I have also run the code & got the answers 23.35 & 23.34 I do not know why
0

LVL 43

Expert Comment

ID: 6326289
deighton, I think the answer lies in the link I posted, as the number is stored as a floating point value, it cannot be guaranteed to be calculated to a given precision:

23.345 may be stored as 23.34499999999 which even when you add .005 to it will still be 23.3499999999 and go DOWN to 23.34 the suggested workarounds are to use either decimal or currency types which are accurate to 28 and 4 decimal places respectively.
0

LVL 3

Expert Comment

ID: 6326295
deighton,

It's because VB uses this silly "banker's rounding"..... The Format method I'm proposing uses the proper rounding that all other programming languages use.

Pino (caraf_g = modder) And before anyone shouts "foul", I'm an EE moderator which is the only accepted way of having two accounts. I just can't be bothered logging out and in all the time. If anything I've said helped, please make sure you accept a comment made by caraf_g, not by modder.

modder
Community Support
0

LVL 18

Expert Comment

ID: 6326296
Good point

'here is a clue to what is going on

Private Sub Command1_Click()

Dim k As Double

k = 23.345

Print k * 1000 - 23345

End Sub
0

LVL 5

Expert Comment

ID: 6326300
Hi,

maybe there's a solution like this posted already, but I still had this function to use for rounding lying around:

Public Function Round_TSB(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double
On Error GoTo ErrorHandler
' Comments  : Rounds a number to a specified number of decimal places (0.5 is rounded up).
' Parameters: dblNumber - number to round
'             intDecimals - number of decimal places to round to
'                        (positive for right of decimal, negative for left)
' Returns   : Rounded number
'
Dim dblFactor As Double
Dim dblTemp As Double         ' Temp var to prevent rounding problems in INT()

dblFactor = 10 ^ intDecimals
dblTemp = dblNumber * dblFactor + 0.5
Round_TSB = Int("" & dblTemp) / dblFactor

Exit Function
ErrorHandler:
GenErrorMessage Err.Number, Err.description
Resume Next
End Function

Hope this helps,
Erwin
0

LVL 3

Expert Comment

ID: 6326301
Tim,

Nope. VB does, most definitely, use "banker's rounding" in its native rounding functions.

The fact that it rounds 2.5 to 2 is by design, and quite deliberate.

Luckily you can use the Format function to work around this, as they've decided, for a reason only they know, to allow the Format function to round according to the much wider held convention of 0-4 down 5-9 up.
0

LVL 18

Expert Comment

ID: 6326315
..hold on I ment Tim had a good point, I still don't agree that the bankers rounding is an issue in this case.  Int is designed to round down to the lower integer - it has no provision to round to the nearest even number at all

int (2.5 + .5) comes out as 3 as well

What is going on in this question is that doubles cannot give 100% decimal accuracy
0

LVL 6

Expert Comment

ID: 6326317
Hi caraf_g so you are modder
in my language (dutch) 'modder' means 'mud'
just thought you liked to know :)
0

LVL 3

Expert Comment

ID: 6326321
Hehe...... guess what.... I am Dutch too.......

geintje.... :)

It's quite a cool ID, really. modder = mud, or can be read as "mother" (as in Modder Superior) and so on......
0

LVL 5

Expert Comment

ID: 6326326
Pierrecampe,

I think he already knows...if I remember correctly he's of Dutch origin himself ;-)

Erwin
0

LVL 3

Expert Comment

ID: 6326329
why couldnt they have just kept it simple.

i wrote an invoicing program for a commercial company, i had quite a few problems with that rounding numbers.

i did actually get round this with using simlar suggestions posted.

just my two pennies

Andy
0

LVL 5

Expert Comment

ID: 6326330
Hehe, beat me to it caraf (ach ja, ik ben ook geen 18 meer)
0

LVL 18

Expert Comment

ID: 6326331
It is cint that rounds 2.5 to 2 and 3.5 to 4 - but int is in use here.

cint convention is fair enough, rounding .5 up is going to give overstated values in the long run, bankers don't want to give away 1/2 pennies.
0

LVL 3

Expert Comment

ID: 6326343
No again, deighton. Try this:

Dim fred As Double

fred = 22.225

fred = fred * 2

fred = fred - 44.45

MsgBox fred

fred = 22.225

MsgBox Round(fred, 2)

In the first MsgBox, it clearly shows that it calculates 22.225 * 2 correctly as 44.45. If there were rounding errors, it would have a problem with ( 2 * 22.225 ) - 44.45. It hasn't

But when you use round on 22.225 it shows 22.22, because it used banker's rounding.

Also, try round(fred, 2) for the following values of fred

MsgBox Round(22.225, 2)
MsgBox Round(22.235, 2)
MsgBox Round(22.245, 2)
MsgBox Round(22.255, 2)
MsgBox Round(22.265, 2)
MsgBox Round(22.275, 2)
MsgBox Round(22.285, 2)
MsgBox Round(22.295, 2)
MsgBox Round(22.305, 2)

The pattern is clear:

22.22
22.24
22.24
22.26
22.26
22.28
22.28
22.3
22.3

Banker's rounding it is.
0

LVL 3

Expert Comment

ID: 6326345
Jij ook, TheMek.....? Gezellig onderonsje, hier. <g>
0

LVL 3

Expert Comment

ID: 6326349
PS, dgb, I just realised that TimCottee, in his two links first mentioned this banker's rounding, so he deserves the points.
0

LVL 18

Expert Comment

ID: 6326391
caraf_g,

but that is a question of the 'round' function which isn't what the questioner is using i nthis case.
0

LVL 3

Expert Comment

ID: 6326589
deighton, it's not just the round function. All VB's intrinsic rounding is done by this rule, whether you use Round, CInt, CLng or whatever.... :(
0

LVL 18

Expert Comment

ID: 6326643
ok fair enough he mentions the 'round' function, but then the next bit refers to 'int'
0

LVL 4

Expert Comment

ID: 6326993
Anyone care to test and/or comment this one ???
TIA

Private Function RoundX(aNumber As Double, aDigits As Long) As Double
Dim vNumber As String
Dim vDecimalSep As String

vDecimalSep = Mid(Format(1000, "0,000.0"), 6, 1)

vNumber = CStr(aNumber * (10 ^ aDigits))
vNumber = Mid(vNumber, InStr(vNumber, vDecimalSep) + 1)

If vNumber = "5" Then
aNumber = aNumber + (10 ^ (-aDigits - 2))
End If

End Function
0

LVL 4

Expert Comment

ID: 6327001
aNumber = aNumber + ((10 ^ (-aDigits - 2)) * IIf(aNumber > 0, 1, -1))

This should replace the one line above, for negative figures
0

LVL 70

Expert Comment

ID: 6327398
0

LVL 22

Expert Comment

ID: 6327511
On a related topic, try this:

dim Ans as integer
Ans = 5/3
print 5\3, int(5/3), cint(5/3), Ans

Result:
1  1  2  2

If you replace all 5's with 8's, you get
2  2  3  3

There seems to be some rounding up going on here.

Even worse, if you replace Ans=5/3 with Ans=23.345*100, it prints 2334

But this works:
Dim Ans As Integer
Ans = 23.345 * 1000
Print Ans, Int(Ans / 10 + 0.5) / 100
0

LVL 3

Expert Comment

ID: 6327593

how many other ways can it be show, ive never seen so many ways to do something,

its gonna be quite hard to give the points, because i think your all right in one way,

its quite funny seeing you all show examples. its interesting, all the different ways to do the same thing.

just my two pennies
Andy
0

LVL 70

Expert Comment

ID: 6327659

Last 10 Grades Given B B A A A A B B B A
0

LVL 22

Expert Comment

ID: 6327729
Did anyone actually offer a solution: "Is there a good resolution for rounding double's"?
0

LVL 70

Expert Comment

ID: 6331549
<Did anyone actually offer a solution: "Is there a good resolution for rounding double's"? >

All these are good solutions! Which result do you want?
0

LVL 70

Expert Comment

ID: 6331551
There is no more one single way of rounding values!
0

LVL 3

Expert Comment

ID: 6333615
Simply put use this:
MyVar = FormatNumber(123.456,2)
0

LVL 70

Expert Comment

ID: 6357969
0

Author Comment

ID: 6499683
It's a bit difficult to choose from so many solutions.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
###### Suggested Courses
Course of the Month16 days, 19 hours left to enroll