Solved

rounding problems

Posted on 2001-07-27
39
598 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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 70

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 70

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 70

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 70

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

688 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