In switzerland, we like rounding monetary numbers to 2 decimal places, with multiples of 5 for the fractional part. This is (probably) because our (physical) money has no “cent” coins, but we have 5 cents coins. That’s commonly called “5 cents” rounding.
Example:
 1.6777777 becomes 1.70
 1.4444444 becomes 1.45
 1.0333333 becomes 1.05
Years ago, I was “gifted” with the knowledge of a “magic” trick to achieve that.
And that’s how it was gifted to me:
multiply by 20, cut the integer part, compare to .5 to do the rounding, adding 1 if it equals or overflows .5, and finally divide by 20 for the result.Pretty simple. It works. However, this may not be a so easy task to do in VB/A as it seems to be.
Here’s my version of this algorithm, in the form of my FiveCt() function.
Note that I do use Double to Currency type conversion in this code, it will also work with Single type values.
You can use this function in Excel or Access (or any VB/A) calculations, as in Access queries for example.
FiveCt()
Round a double precision number to a five cents value.
Double in, Double out.
1 2 3 4 5 6 7 8 9 10 11 12 13 
Public Function FiveCt(ByVal dblAmount As Double) As Double Dim curResult As Currency curResult = CCur(dblAmount) * 20 If curResult  Int(curResult) >= 0.5 Then curResult = Int(curResult) + 1 Else curResult = Int(curResult) End If curResult = curResult / 20 FiveCt = CDbl(curResult) End Function 
Notes and references on floating point variable types in VB/A

Currency [64 bits (8 bytes)]
Currency variables are stored as 64bit (8byte) numbers in an integer format, scaled by 10,000 to give a fixedpoint number with 15 digits to the left of the decimal point and 4 digits to the right.
This representation provides a range of:
922,337,203,685,477.5808 to 922,337,203,685,477.5807.
?CCur(1/6)
0.1667 
Double (doubleprecision floatingpoint) [64 bits (8 bytes)]
Variables are stored as IEEE 64bit (8byte) floatingpoint numbers ranging in value from:
1.79769313486231E308 to 4.94065645841247E324 for negative values 4.94065645841247E324 to 1.79769313486232E308 for positive values
?CDbl(1/6)
0.166666666666667 
Floats (single or double) VS Currency discussion on stack overflow: “Use the Currency data type to avoid rounding errors when precision is of the utmost importance.”
Recent Comments