Round() fn rounds down at 5

[jeffmoore]

I'v noticed that the round fn rounds down sometimes. Code is in a control;

=round([OriginalHeight3]*0.5,2)

where OriginalHeight3 = 26.410

the number returned is 13.2

it should be 13.21

the standard rule for the round fn is;
round down at < .5
round up at >= .5

Am I missing something here???
TIA
Jeff

 

[BSman]

Your equation is incomplete. It should read:

=round(.05+[OriginalHeight3]*0.5,2)

This will return a properly rounded value to 2 decimal places, so the result of your example would be 13.21.

 

[KornGeek]

Check out Thread181-669180 for more info on how the round function works and how to make it behave as you expect.

 

[jhackbarth]

I recognize that the rounding function is working as designed.

By convention, rounding up odd numbers such as Round(0.15,1)=0.2, and rounding down even numbers such as Round(0.25,1)=0.2 creates an unbiased set.

The number 0.5 is equidistant from 0 and 1, so half the x.5 numbers are rounded up, the other half are rounded down.

If you force x.5 to always round up, then you are biasing the data high.

 

[jeffmoore]

Well I see you point on the the number .5 being eqidistant from 0 & 1. But.. Good old high school math taught the rules that I presented. This remained true all the way thru my engineering days at college.
Jeff

 

[KornGeek]

Here is an excerpt from one of my posts in the thread I mentioned above:

Here is a little function I threw together to do the rounding for me (warning: Has not been thoroughly tested; only good to 4 decimal places)

Public Function MyRound(dblValue As Currency, lngPlaces As Long) As Currency
Dim offset As Currency
Dim shift As Long
Dim n As Long

n = 0
shift = 1
While (n < lngPlaces)
shift = shift * 10
n = n + 1
Wend
offset = 0.5 / shift
MyRound = Int((dblValue + offset) * shift) / shift
End Function


Interestingly enough, if you change the currency data type to double, it no longer functions properly.

 

[psemianonymous]

This method of rounding is called banker's rounding. I first heard mention of it on Slashdot, of all places, when one story submission was basically &quot;VBA is buggy, check out its crappy rounding function&quot;, and then the posters proceeded to insult the story's original submitter.

The moral of this story is that, though we were taught that .5 is ALWAYS rounded up, it is also a convention to round .5 to the nearest even number.

So if you want to round it up, you can use a custom function, or whatever. Just acknowledge that this strange way is also a valid way to round numbers, in addition to what we've been taught.


Pete

 

[Hmadyson]

To round I usually put a reference to the version of Excel on my computer, and then create a wrapper function:

Function XLRound(Num as Double, NumDigits as integer) as double
xlround=excel.worksheetfunctions.round(num,numdigits)
end function

This is easy and makes everything round correctly like it does in XL.

 

[jeffmoore]

So I presume that Excel rounds 'conventionaly' as opposed to 'even up' rounding as in access?
Next thought....
Why would you do something one way in one application and another in a related app?
Further more....
Isn't Excel more commonly used by businesses (read finacial) and hence the 'even up' rounding would make more sense there.
If I read the above arguements correctly the 'even up' method is used by finacial institutions.
May be I should write some banking software and redistribute that half cent...
Jeff

 

[bhoran]

Jeffmoore

That has already been done. I think it was in the 60's it was dubbed the &quot;Salami Shaving Case&quot; basically someone did as you are saying but only stole a tiny decimal and cyphoned it off, the money generated was beyond all expectations of the people involved and their accounts increased so quickly was picked up (prob by the money laundering watchdogs) I can't remember all the details now but we studied it at school.

 

[psemianonymous]

Wow! I saw the same thing happen in a film documentary. I think it was called &quot;Office Space&quot; or something.


Pete

 

[jeffmoore]

There was a richard pryor movie too....

 

[jeffmoore]

But lets answer the first two of my questions....
Jeff

 

[BSman]

OK, here's another way to round, once you figure out how you want to round (i.e., .5 rounded to next whole number or only .51 and up rounded up). The basic trick is to convert to an integer, get rid of any decimals, then convert back to the correct number of decimal places.

So if you want two decimal places in your answer, you multiply the starting number by 100 and make an integer (or long) equal to it. But to do the rounding you muliply by 100 and add .5 (or .51 or whatever will end up with the type of rounding you want). Then the integer will be the rounded value. The result you want (as a single or double or currency) will be the integer divided by 100.

If you are worried about the integer being also rounded internally, use the function that will take just the numbers to the left of the decimal place (I forget what it is, but you can look it up in Access/VBA help). By doing some simple tests you can be sure that you will get the exact results you want.

This method works correctly for whatever number of decimal places you need. Just use 10 or 1000 or whatever.

It's also the simplest and cleanest solution and lets us precisely control the results.