rounding problems

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?
dgbAsked:
Who is Participating?
 
caraf_gConnect With a Mentor Commented:
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
 
OtanaCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
deightonprogCommented:
'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
 
andyclapCommented:
Have you tried VB's in-built Round function?
e.g.
Round(x,dec)
0
 
deightonprogCommented:
...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
 
caraf_gCommented:
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
 
vindevogelCommented:
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
 
deightonprogCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
modderCommented:
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
 
deightonprogCommented:
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
 
TheMekCommented:
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
 
modderCommented:
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
 
deightonprogCommented:
..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
 
pierrecampeCommented:
Hi caraf_g so you are modder
in my language (dutch) 'modder' means 'mud'
just thought you liked to know :)
0
 
modderCommented:
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
 
TheMekCommented:
Pierrecampe,

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

Erwin
0
 
andysalihCommented:
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
 
TheMekCommented:
Hehe, beat me to it caraf (ach ja, ik ben ook geen 18 meer)
0
 
deightonprogCommented:
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
 
modderCommented:
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
 
modderCommented:
Jij ook, TheMek.....? Gezellig onderonsje, hier. <g>
0
 
modderCommented:
PS, dgb, I just realised that TimCottee, in his two links first mentioned this banker's rounding, so he deserves the points.
0
 
deightonprogCommented:
caraf_g,

but that is a question of the 'round' function which isn't what the questioner is using i nthis case.
0
 
modderCommented:
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
 
deightonprogCommented:
ok fair enough he mentions the 'round' function, but then the next bit refers to 'int'
0
 
vindevogelCommented:
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
 
vindevogelCommented:
aNumber = aNumber + ((10 ^ (-aDigits - 2)) * IIf(aNumber > 0, 1, -1))

This should replace the one line above, for negative figures
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
rspahitzCommented:
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
 
andysalihCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
rspahitzCommented:
Did anyone actually offer a solution: "Is there a good resolution for rounding double's"?
0
 
Éric MoreauSenior .Net ConsultantCommented:
<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
 
Éric MoreauSenior .Net ConsultantCommented:
There is no more one single way of rounding values!
0
 
BGillhamCommented:
Simply put use this:
MyVar = FormatNumber(123.456,2)
0
 
Éric MoreauSenior .Net ConsultantCommented:
Common dgb. You surely had your solution in all that comments!
0
 
dgbAuthor Commented:
It's a bit difficult to choose from so many solutions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.