Rounding a Value

[George1111]

I was trying to round off a Money Value in a Currency field

eg,

Dim MV as Currency
A = 3.00
B = 1.10

MV = (A * B) + 0.005
MV = MV / 100
MV = MV * 100

Result = 3.31 !

Currency Variables must therefore have their own rounding rules

I overcame the problem with

Dim MV as Currency
A = 3.00
B = 1.10

MV = (A * B) + 0.005
MV = (Int(MV * 100)/100)

Result = 3.30 Correct


Can anyone suggest a better way to deal with this common calculation requirement

[Comintern]

Check out this link for more information than I've ever seen in one place before about how MS products do rounding.

http://support.microsoft.com/default...b;en-us;196652

[Patzer]

Hi George,

to round 3.305 to 3.31 is absolutely correct (based on 2 decimals), I just do not understand why you want to get the wrong result??

[Pinky98]

I agree... what exactly is the issue here? What was wrong with your results?

FYI: There are several different ways of rounding off: Round, Int, Floor and Ceil.

[Comintern]

Actually, the "correct" result of rounding depends on the application, not on absolute rules. This is especially important in accounting functions, as "rounding bias" can start to introduce error over large amounts of transactions. The link above gives 10 different code examples of rounding appropriate for different business rules.

[DeepButi]

Take a look here for a small surprise.

Hope it helps

[Patzer]

@deepbuti:
AFAIK the round function of Microsoft is just buggy (not only in the context you've described in the link you gave), just don't use it.
It is much better to write your own round function, it's easy.

[Pinky98]

It is not buggy. It is a method of rounding known as "bankers rounding". Why I wouldn't have the first clue.

Believe it or not, it does have its uses. Most noticably it can be implemented to be very quick.

You can wriet your own function but they are often very slow in comparison, which is usually fine for VB (since, if speed was an issue, you wouldn't use VB).

Often "accurate" rounding is not AS necessary as one might think. It will often surfice to simply use bankers rounding or return only the integer part. But it does depend on your requirements.

[George1111]

I thought Currency variables worked to 4 decimal places

Hence in my example I am applying the 0.005 to achieve rounding (up or down) because the currency variable doesn't know I want rounding to 2 decimal places

The original problem was that I also wanted to truncate any superfluous places in the calc (I only want a 2 decimal result ie, Dollars and Cents)

What became interesting was that if my result of the calculation was 3.3103
after I had applied the 0.005 round off factor it became 3.3153, the Currency field then applied rounding again if I tried to chop off the last 2 decimals by dividing the whole value by 100 - this caused an extra rounding to take place.

As for the IEEE standards - all I can say is YEEEEEEEEEEEEEEEEEEEEE !

I was hoping to see something like the Math.Round formula out of interest

(we can always write our own, to be sure)

Thanks for the comments guys - most interesting !