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.
- 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.
Round a double precision number to a five cents value.
Double in, Double out.
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
curResult = Int(curResult)
curResult = curResult / 20
FiveCt = CDbl(curResult)
Notes and references on floating point variable types in VB/A
Currency [64 bits (8 bytes)]
Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point 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.
Double (double-precision floating-point) [64 bits (8 bytes)]
Variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from:
-1.79769313486231E308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232E308 for positive values
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.”