Standard Plain-ole rounding question

SethEden

New Member
Joined
Aug 13, 2007
Messages
14
Ok sorry I had to start another post here, I couldn't find one that covered my problem.

I was reading that the "ROUND" function in Excell uses bankers rounding and all I want to do is conventional rounding, like the kind your learned about in 6th grade to the nearest penny.

Example:

4.135 should round to 4.14
4.134 should round to 4.13

I'm writing some really important government financial software so I want to make sure I get this right.

Would the normal "=ROUND(A1,2)" work in this case with the above described behavior? I want to make sure I've got my understanding correct and that I won't end up with half the answers being wrong from accedently using Bankers rounding.

Thoughts?

Cheers
Seth
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Seth

From my understanding of Bankers' Rounding, both of your examples would return what you consider to be the correct result. With Bankers' Rounding, however, the following would be different:

4.125 .... would return 4.12
4.124 .... would also return 4.12

However, this isn't the behaviour I am observing in MS Excel: 4.125 gets rounded to 4.13.

So maybe I have made a mistake in my understanding of BR?

Anyway, you could use another formula which avoids the issue:

=FLOOR(A1+0.005,0.01)
 
Upvote 0
Thanks, but...problem

Thanks for that, but I've already looking into such a similar solution.

The with adding a small amount to the cell before rounding doesn't actually fix the problem. Because values of say 4.120 would have 0.005 added to them and the rounding problem is the same as previously stated.

In the case of adding a small amount, even 0.00001 seems to work in that case, and is less significant but seems to work just fine. However to actually get this problem solved under Bankers Rounding you have to only add that small amount if the value in the 3rd digit is a 5, for rounding to 2 decimal places. Then it would work, however since my sheet grows dynamically from a database and I'm defining the cell equation in VBA it would make the equation rather complex. Anything that works I'm good with, but I'd like to try and keep it simple if possible.

So you don't think the "ROUND" function is using Bankers Rounding?

Thanks for the response.

Seth
 
Upvote 0
Seems your right

I just typed a long string of 4 digit numbers into Excel and tested out the standard plain ole "ROUND" and it does seem to work as advertised.

Maybe I'm getting confused between the Equation "ROUND" in the sheet and the VBA Rounding functions.
:unsure:
:rolleyes:

That's Microsoft for ya!
:cool:

Thanks again for the response, got me thinking. I think I'm good, unless I find out that this isn't what I really need, but that's what I've been told is in fact needed.

Cheers
Seth
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top