# Standard Plain-ole rounding question

#### SethEden

##### New Member
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Richard Schollar

##### MrExcel MVP
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)

#### SethEden

##### New Member
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

#### SethEden

##### New Member

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.

That's Microsoft for ya!

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

#### mortgageman

##### Well-known Member
1) FWIW, and not that there was any real doubt about it anyway, Excel uses the "standard" or common method of rounding.
see the following
http://en.wikipedia.org/wiki/Rounding

2) When Richard gives the answer - you're done. No need to check any futher.

#### Richard Schollar

##### MrExcel MVP
2) When Richard gives the answer - you're done. No need to check any futher.

Gene - I wish but it's so not true!!

#### SethEden

##### New Member
Thanks guys!

Ok, thanks for that Wiki link that puts the problem to bed for good!

I was getting confused with this:
http://support.microsoft.com/kb/194983

I guess I didn't read it carefully enough.

Cheers
Seth

#### mortgageman

##### Well-known Member
2) When Richard gives the answer - you're done. No need to check any futher.

Gene - I wish but it's so not true!!

Well, lets just say that I wish I was as wrong as "much" as you.

1,190,610
Messages
5,981,916
Members
439,743
Latest member
KatieO

### 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?

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