Banker's Round / Half to Even Rounding problem

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
I had a need for a Banker's rounding formula (aka Half Round to Even) so I did some searching and found a Mr. Excel video referencing a formula by Barry Houdini. My need was to round figures to the nearest even penny. (5.145 rounds to 5.14 and 5.155 rounds to 5.16) This is designed to keep the overall total close to the actual figure by sometimes rounding up and sometimes rounding down on an exact half cent.

The formula in question is: =ROUND(A1,2)-(MOD(A1,10)*1000,20)=5)/100

I found an interesting problem with it when the figure being rounded is based upon a calculation vs. just a number. In cell A1, I have a calculation and in cell A2, I have the same number just keyed in with no formula. One works and one doesn't and I can't see why. Any help would be appreciated. Thanks
Book2
ABCD
16.52500000000000006.52
26.52500000000000006.53
3
4TRUE
Sheet2
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
The formula in question is: =ROUND(A1,2)-(MOD(A1,10)*1000,20)=5)/100

Sorry, this should have been: =ROUND(A1,2)-(MOD(A1*1000,20)=5)/100
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,824
Office Version
  1. 2010
Platform
  1. Windows
As reluctant as I am to modify one of barry's formulas, maybe

=ROUND(A1,2) - (MOD(ROUND(A1, 4) * 1000,20)=5)/100
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
As reluctant as I am to modify one of barry's formulas, maybe

=ROUND(A1,2) - (MOD(ROUND(A1, 4) * 1000,20)=5)/100


shg.... Sorry, just getting back to this thread. After posting my original post, I did try the same thing you did but I rounded A1 to 5 spaces which worked. I then changed it to 10 just to be safe and it still worked so I left it at that. I guess it has something to do with the calculation. ??

Thanks for the help
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,824
Office Version
  1. 2010
Platform
  1. Windows
If you were doing this in VBA with data type Currency, the least significant bit is exactly $0.0001 -- that's why I chose four decimals. If 10 works for you, that's fine, but if the dollar values are > $10,000, rounding to 10 decimals doesn't do anything.
 

Forum statistics

Threads
1,147,514
Messages
5,741,607
Members
423,672
Latest member
ajj13

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
Top