Solved

rounding problems

Posted on 2001-07-27
39
568 Views
Last Modified: 2012-06-21
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
Comment
Question by:dgb
39 Comments
 
LVL 11

Expert Comment

by:Otana
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

by:TimCottee
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

by:deighton
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

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

Expert Comment

by:deighton
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

by:
caraf_g earned 100 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.

See also a question I posted about this quite a while back:

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

by:caraf_g
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

by:vindevogel
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

by:deighton
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

by:TimCottee
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

by:modder
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

by:deighton
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

by:TheMek
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

by:modder
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

by:deighton
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

by:pierrecampe
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

by:modder
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

by:TheMek
ID: 6326326
Pierrecampe,

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

Erwin
0
 
LVL 3

Expert Comment

by:andysalih
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 5

Expert Comment

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

Expert Comment

by:deighton
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

by:modder
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

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

Expert Comment

by:modder
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

by:deighton
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

by:modder
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

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

Expert Comment

by:vindevogel
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
   
    RoundX = Round(aNumber, aDigits)
   
End Function
0
 
LVL 4

Expert Comment

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

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

Expert Comment

by:Éric Moreau
ID: 6327398
0
 
LVL 22

Expert Comment

by:rspahitz
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

by:andysalih
ID: 6327593
blinking heck,

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 69

Expert Comment

by:Éric Moreau
ID: 6327659
Please maintain.

Questions Asked 79
Last 10 Grades Given B B A A A A B B B A  
Question Grading Record 67 Answers Graded / 67 Answers Received
0
 
LVL 22

Expert Comment

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

Expert Comment

by:Éric Moreau
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 69

Expert Comment

by:Éric Moreau
ID: 6331551
There is no more one single way of rounding values!
0
 
LVL 3

Expert Comment

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

Expert Comment

by:Éric Moreau
ID: 6357969
Common dgb. You surely had your solution in all that comments!
0
 

Author Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now