Rounding Numbers

General
1

I am trying to round numbers to the nearest 10th.
Running the following code generated the results to the right.
msgbox str(format(2.15, “0.0”)) = 2.1
msgbox str(format(2.25, “0.0”)) = 2.2
msgbox str(format(2.35, “0.0”)) = 2.4
msgbox str(format(2.45, “0.0”)) = 2.5
msgbox str(format(2.55, “0.0”)) = 2.5
msgbox str(format(2.65, “0.0”)) = 2.6
msgbox str(format(2.75, “0.0”)) = 2.8
msgbox str(format(2.85, “0.0”)) = 2.9
msgbox str(format(2.95, “0.0”)) = 3.0

Am i missing something here on lines 1, 2 , 4 and 5 of this code???
Any ideas on why this happens??

2

2.25 is stored as a double, and is actually 2.24999(repeat), so it rounds down.

Try this (I’d recommend a function to do it, though):

  dim d as Double = 2.25
  d = Round(d*10)/10
  msgbox str(format(d, "0.0"))
3

or if you know for a fact that you will never need a precision greater than 2 digits past the decimal, then use the CURRENCY datatype instead of double, and you will not have this problem

4

From the LR:

Currency
This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.

5

You can also do it this way.

msgbox str(format(2.15 + 0.05, "0.0"))

This will always give you the correct answer.
It’s a well known programming trick from the time I was programming in Dibol and Cobol.
(I’m getting old. ;))

6

[quote=97198:@Paul Sondervan]You can also do it this way.

msgbox str(format(2.15 + 0.05, "0.0"))

This will always give you the correct answer.
It’s a well known programming trick from the time I was programming in Dibol and Cobol.
(I’m getting old. ;))[/quote]
Actually, if you try “format(2.20 + 0.05, “0.0”)”, it doesn’t work. You still get 2.2.

7

[quote=97191:@Andre Kuiper]From the LR:

Currency
This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.[/quote]

Confused… are you advocating that this is NOT a viable solution…

8

You are right.
I forgot 1 zero.
The correct code is:

msgbox str(format(2.15 + 0.005, "0.0")) = 2.2

(2.14999 becomes 2.15499 → 2.2 rounded)

9

Dave, no, just pointing out that the currency variable has 4 decimal digits instead of 2 as you mentioned.

Pls. try this:

[code] dim c as Currency =1234567890.1266

MsgBox str(c,“#,.00”)[/code]

and you will see that the problem still is there.

The (corrected) version of Paul’s example is IMHO a right one solution.

10

Thanks guys for your responses. Adding .005 before rounding seems to be the best fix and works fine. So extra thanks to Paul.

11

Try this function:

Function RoundDblToNplaces(origNum as Double, places as Integer) As Double
dim roundedNum as double

// get adjustment value
dim s as string = “.”
For i as integer = 1 to places
s = s + “0”
Next
s = s + “5”
Dim adjustmentValue as Double = Val(s)

// get format string
Dim formatString as string = “#######.”
For i as integer = 1 to places
formatString = formatString + “0”
Next
Dim formattedNumString as String = Format(origNum + adjustmentValue, formatString)

// round
roundedNum = Val(formattedNumString)

return roundedNum
End Function

12

Rounding can get rather complicated. See here.

Peter.