Look Who it Was Random video

Excel - Master Excel Rounding: BankerRound Function Revealed - Episode 1047

1,506 views · Published 30 June 2009 · 3:32 · Indexed 25 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Excel Rounding: BankerRound Function Revealed.

Welcome back to the MrExcel netcast, where we tackle all things Excel. In this episode, we're diving into the topic of rounding correctly. As Excel users, we often deal with massive amounts of data and it's important to know how to analyze it accurately. So, let's fire up a pivot table and see if you can solve this rounding problem.

In our previous episode, we discussed the esoteric topic of rounding when the last significant digit is a five. I showed how the traditional method of rounding up to the next whole number can introduce bias. That's where the ASTM E 29 rule comes in, stating that when the last digit is a five, we should round towards the even number. In yesterday's podcast, I shared a VBA code that rounds correctly, but only when the precision is 0, 1, 2, 3, 4, and so on. But what about when the precision is negative? That's where my new function, BankerRound, comes in.

BankerRound is a simple function that takes two arguments: the number we want to round and the precision. If the precision is greater than or equal to 0, the function simply uses the VBA round function. But if the precision is negative, the function makes some adjustments to the number before rounding it. For example, if we want to round to the nearest tenth (precision of -1), the function multiplies the number by 0.1, rounds it using the VBA round function, and then divides it by 0.1 to get the original number back. This ensures that the rounding is done correctly, according to the ASTM E 29 rule.

To demonstrate the effectiveness of BankerRound, I set up a few test cases. We have numbers like 5.15 and 5.25, which round to 5.2 when rounded to one decimal place. But when we round to the nearest hundred, we see the real difference between traditional rounding and the ASTM E 29 rule. In school, we were taught to round 5 up to 10, but according to the rule, it should be rounded towards the even digit, which in this case is 0. Similarly, 15 should be rounded up to 20, not 10. BankerRound takes care of these scenarios and ensures that the rounding is done correctly.

While BankerRound may not be as fast as the VBA round function, it is a valuable tool to have in your Excel arsenal. Especially if you need to adhere to the ASTM E 29 rule, this function will come in handy. So, thank you for tuning in to this episode of the MrExcel netcast. Don't forget to check out our previous episode for more details on rounding correctly. And as always, stay tuned for more Excel tips and tricks from MrExcel. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents
(00:00) Bankers Rounding in Excel
(00:33) School taught that 5 should round up to next number
(00:43) But ASTM E29 says round towards the even.
(01:03) VBA Function BankerRound
(01:35) How the VBA works
(02:17) Testing the results
(02:55) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding rounding function to project
ASTM E 29 rounding rule
BankerRound function
Pivot table analysis
Precision in rounding
Rounding algorithm
Rounding to the nearest 10
Rounding to the nearest even number
Rounding to the nearest hundred
Rounding to the nearest whole number
Test cases for rounding
VBA round function

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152494/



After yesterdays podcast about ASTM E29 rounding, I produce a function in VBA that will correctly do the bankers rounding algorithm in Excel. Episode 1047 shows you how.

More from this channel