VBA Bankers Rounding problem

Hi,

I believe bankers rounding rounds up when the rounded figure is 5 or above?

I would suspect it's an issue with the binary representation of the decimal value. It may be that those numbers are in fact internally represented as 0.712631250000001 for example, so the banker's rounding is doing what it should.

I believe that Excels 'round' function just uses standard 'rounding rules', and it is rounding perfectly as expected.

The standard rounding rules are that, for example, if you are rounding to 7 decimals;
If the 8th decimal is 5 or above the 7th decimal is increased by one
If the 8th decimal is 4 or less the 7th decimal remains as it was.

Which is not 'Bankers Rounding' which is why your values are 'incorrect'.

In Bankers Rounding I believe that if the 8th decimal is a 5, then if the 7th decimal is odd it is rounded up, and if it is even it is rounded down, (so the 7th decimal is effectively rounded to it's nearest even denomination. - Do a 'Find' on this wiki page for 'Banker' for the full description: http://en.wikipedia.org/wiki/Rounding )
This means there are a few things you need to check before you do your rounding;
- Firstly is the 8th decimal a 5? If not just round normally
- Secondly, if it is a 5 then the rounded figure will depend on whether the 7th decimal is odd or even.

With that in mind try the following code in a cell, (presuming the value that you want to round is in A1):

=IF(RIGHT(ROUND(A1,8),1)="5",MROUND(A1,2*10^-7),ROUND(A1,7))

Note
BLUE references are the number you want to round.
RED digits are the number of decimals you will round to.
DARK RED digits are the number of decimals you will round to +1.

Please try this on a worksheet and see if it gives the results you expect.
If it works and you need it as VBA code let me know

I'm afraid the MROUND() function doesn't work. I'm getting a #NAME? error. Just found the reason.
"If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in." Not sure if I can get this installed on my work machine. Hang on, got it installed. I'll check it out again. But I see what you're doing with the formula.

I'd like it in VBA code too if that's ok.

You mentioned "- Firstly is the 8th decimal a 5? If not just round normally"
Some of the numbers will have 8, 9 or 10 decimal place with the intention of rounding to 7. So will your formula still work if it only checks if the 8th digit is 5?
Can you update the formula to let's say check all the digits after the 7th decimal place and if the result is 5 and only 5, i.e. not 50001, then use the correct rounding?

If the number is say 1.1234566595, this would round to 1.1234567

Originally Posted by sflemings

You mentioned "- Firstly is the 8th decimal a 5? If not just round normally"
Some of the numbers will have 8, 9 or 10 decimal place with the intention of rounding to 7. So will your formula still work if it only checks if the 8th digit is 5?
Can you update the formula to let's say check all the digits after the 7th decimal place and if the result is 5 and only 5, i.e. not 50001, then use the correct rounding?

The first thing my code does is round the number to 8 decimal places using normal rounding, and then inspects the last digit, and performs the neccasary rounding function based on that, so it shouldn't matter about having additional decimal places.

The best thing is for you to try it and see if it gives the results you expect. It seems to work fine here, but I am no Banker

The difference is due to binary numbers though - you wouldn't see it just from typing values into cells as Excel will compensate. Try this test:

Sub testoutput()
   Dim dblTemp As Double, dblDiff1 As Double, dblDiff2 As Double
   dblTemp = 0.71263125
   dblDiff1 = 0.7126313 - dblTemp
   dblDiff2 = dblTemp - 0.7126312
   Debug.Print dblDiff1; dblDiff2
End Sub

You will note that dblDiff1 is smaller than dblDiff2, and hence the rounding goes up. It is doing Banker's rounding as expected, it's just that the values it's working with are not quite what you think they are!

X-post here, BTW.

Originally Posted by sflemings

Is it possible to take the Math.Round() VB formula and stick it in as a macro? Would that make any difference or would it just be the same result.

It would give the same result.
The ROUND function is not broken! It is doing exactly as it is supposed to, it is rounding using conventional rulings.
What you are wanting though is BANKERS ROUNDING, which is not what the ROUND function does, hence you get the 'wrong' result.
Thats why I had to generate a custom formula for you.

If you want it in VBA format, (and also don't want to need the Analysis toolpak), then paste the following code into a VBA module in your workbook:

Option Explicit

Function BankersRound(num_to_round As Double, decimal_places As Long)
' Will perform bankers rounding on the number passed.

If Right(Round(num_to_round, decimal_places + 1), 1) = "5" Then
    BankersRound = Round(num_to_round / (2 * 10 ^ -decimal_places), 0) * (2 * 10 ^ -decimal_places)
    Exit Function
Else
    BankersRound = Round(num_to_round, decimal_places)
    Exit Function
End If
End Function

You can then use it in your worksheet like:

=BankersRound(A1,7)

or

=BankersRound(0.123456789, 7)

Or you can use it in your vba code:

Dim result as double
double = BankersRound(Range("A1").Value, 7)

or

Dim result as double
double = BankersRound(0.123456789, 7)

Try it and let me know how you get on please

Try this:

Function BankRound(num, dp) As Double
   BankRound = Round(CDec(num), dp)
End Function

In that case I applogise as it seems I didn't fully understand how the rounding of the additional decimals should be processed.
Romper's code seems to work well on the numbers I have tested, and it is also simple and elegant, (so much so that I am now going to sit and try to work out exactly what it is doing )

EDIT

It appears that I also didn't realise that the VBA Round function behaves differently to the worksheet Round() function
Not my day today
Can I ask, where does it describe the difference between the worksheet round function, and then VBA round function? I'm wondering now where else I may have made mistakes in my own code due to this

I think this is kind of an academic exercise, considering that your computer stores numbers in binary floating point (IEEE 754-1985) format rather that decimal floating point (IEEE 854, or IEEE 754-2008). For example,

Function BRound1(d As Double, np As Long) As Double
    BRound1 = Round(CDec(d), np)
End Function

Function BRound2(d As Double, np As Long) As Double
    BRound2 = Round(d, np)
End Function

Sub test()
    Const np        As Long = 9

    Dim i           As Long
    Dim d           As Double

    For i = 1 To 10000000#
        d = CDbl(Rnd) * CDbl(Rnd)
        If BRound1(d, np) <> BRound2(d, np) Then
            Debug.Print "i = " & i & vbLf & _
                        "                  123456789012345" & vbLf & _
                        "d =             " & d & vbLf & _
                        "Bround1(d, " & np & ") = " & BRound1(d, np) & vbLf & _
                        "Bround2(d, " & np & ") = " & BRound2(d, np) & vbLf & vbLf
            Stop
        End If
    Next i
End Sub

... stops at

i = 5272390
decimals:         123456789012345
d =             0.627676316500001
Bround1(d, 9) = 0.627676316
Bround2(d, 9) = 0.627676317

hyt,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.