CODEWITHSUNDEEP
vbamathexcel
Rustam
Rustam

Reputation: 1776

VBA Ultimate rounding

I've read much about rounding in Excel. I found out that VBA's Round() function uses "Bankers rounding" while Application.WorksheetFunction.Round() uses more or less "normal" rounding. But it didn't help me to understand this:

? Round(6.03499,2)
 6.03

Why? I want to see 6.04, not 6.03! The trick is that

? Round(Round(6.03499,3),2)
 6.04

I thought a bit and developed a subroutine like this:

Option Explicit
Function DoRound(ByVal value As Double, Optional ByVal numdigits As Integer = 0) As Double
   Dim i As Integer
   Dim res As Double
   res = value
   For i = 10 To numdigits Step -1
       res = Application.Round(res, i)
   Next i
   DoRound = res
End Function

It works fine.

? DoRound(6.03499,2)
  6.04

But it is not cool. Is there any built-in normal rounding in Excel?

Upvotes: 1

Views: 1078

Answers (2)

TheLaurens
TheLaurens

Reputation: 414

Rounding 6.0349 to two decimals is just not 6.04 hence, no, there is no such function. Round up will round anything up. Hence, 6.0000000001 will also become 7 if you round to 0 decimals.

Upvotes: 1

Kentora
Kentora

Reputation: 106

If you round 6.03499 to 3 digits it will be 6.035 - which is correct.
If you round 6.03499 to 2 digits it will be 6.03 - which is correct

However - the example where you first round to 3 digits, then to 2 is also correct, by the following statement:
Round(6.03499, 3) gives 6.035
Round(6.035, 2) gives 6.04

If you want Round(6.03499, 2) to give 6.04 you have to use Application.WorksheetFunction.RoundUp

Upvotes: 3

Related Questions