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):

Please Login or Register  to view this content.

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

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:

Please Login or Register  to view this content.

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

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

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:

Please Login or Register  to view this content.

You can then use it in your worksheet like:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Or you can use it in your vba code:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Try it and let me know how you get on please

Try this:

Please Login or Register  to view this content.

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,

Please Login or Register  to view this content.

... stops at

Please Login or Register  to view this content.

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.