You can use negative numbers in the ROUND worksheet function to make numbers less precise, i.e.
=ROUND(56652,-3)
returns 57,000
I just learned that the Round method in VBA doesn’t have that feature. “Invalid procedure call or argument” it tells me. I could use the WorksheetFunction object and the Round method that comes with it, but instead I used integer division:
Me.tbxContract.Text = Format((Val(Me.tbxContract.Text) 1000) * 1000, “$#,##0.00”)
more rounding:
worksheetfunction.round(56652, -3)
or
56652 – 56652 mod 1000
Unless I’m mistaken, I don’t believe your integer division is rounding – it’s truncating. So, when given 56652 is will return $56,000, not $57,000.
Eric: No, you’re not mistaken. I’m using the term rounding rather carelessly here.
Another option I’ve used that does actual rounding, not truncation:
round((56662/1000),3)*1000
I like functions for those things.
Function VBRound(D As Double, L As Long) As Double
Dim x As Double
x = 10 ^ L
VBRound = Int(D * x + 0.5) / x
End Function
Best wishes Harald
If it only was that easy
Harald’s function introduces a 6% upward bias
We have 10 digits of which 0 doesn’t need rounding
That leaves 9 digits which introduce an error when rounded.
1 to 4 we round down
6 to 9 we round up
5 we have to assign a 50/50 chance to either up or down.
Assuming a uniform distribution of digits we can look at the digit before the 5 and round up when this is an even digit or round down when this is an odd digit.
Yes, I am aware that most of us remember rounding up from 5 from school math, but it introduces an upward bias (assuming uniformly distributed digits)
Real life data may show different distributions of digits, leading the interesting discussions about pricing practices in supermarkets when at the till there will be a rounding to multiples of 5 cents.
Of course there isn;t a problem in this case when we assume unifrom distributions.
Cheers
And thanks for this blog Dick
Hi AmonRa
My function introduces nada, it it a ROUND replacement. What you talk about is known as “bankers rounding” and if we want bankers rounding and think that ROUND stinks then we write a function that does bankers rounding instead of the stupid ROUND things. Like
Function VBRound(D As Double, L As Long) As Double
Dim x As Double
x = 10 ^ L
VBRound = CLng(D * x) / x
End Function
Best wishes Harald
rounding! so many issues with roundng, there is a good few artiles on MSDN about it, VB and employ diffrent rounding methods, so just be careful!
Harald’s formula is the one I tend to use but be aware it needs ammending if you are rounding negative numbers (instead of adding 0.5 you need to subtract it)
Hi!
I been using this code snippet for random numbers or a slightly changed one. One solution is to randomly set the number to a negative one or a positive one and the problem with getting negative random numbers is solved! Thanks for a interesting site!
http://vbaexcel.eu/vba-macro-code/rnd-random-function