The term “rounding difference” commonly describes something insignificant; it rarely raises cause for concern. However, when you discover that Excel rounding and Power Query rounding are different, you suddenly realize that it can be a bigger issue than you first thought.
This post explains the different rounding methods in Excel and Power Query, and how to ensure they apply the same calculation.
Watch the video
Let’s look at a simple example to illustrate the issue.
Let’s suggest, the entrance exam for a university requires applicants to sit 4 exams. If their rounded average score is 65 or more, they are accepted. Otherwise, they are rejected. Therefore, passing becomes very important.
Here are the average results of 4 applicants.
The requirements were for rounded averages. So, let’s go ahead and round these numbers in Excel and Power Query.
To round the results in Excel, we can enter the following in Cell D3.
This leads to the following result:
So far, no issues. Everybody is going to university.
Power Query rounding
Now let’s load this data in Power Query, and apply the Round transformation found in Add Columns > Rounding > Round…
Set the decimal places to zero.
Finally, close and load the values back to Excel.
In case you didn’t notice, I’ve highlighted the issue. For Sally:
- Excel rounds 64.5 to 65
- Power Query rounds 64.5 to 64
Based on Power Query, Sally will not be going to university after all!
Vera’s score of 69.5 rounds to 70 for both. Why is Sally’s value rounding differently between Excel and Power Query? Let’s see what’s causing this.
Rounding methods in Excel and Power Query
There are different types of rounding being applied.
Excel uses traditional rounding
In school, I’m sure we all learned that when rounding .5, always rounds up. Always! ALWAYS!
I don’t know if this method has an official name, so let’s refer to it as “traditional rounding”.
Excel’s ROUND function applies this methodology. Therefore, the results are:
- 64.5 rounds to 65
- 69.5 rounds to 70
Power Query uses bankers rounding
Power Query uses a different rounding method known as bankers rounding.
I’m sure I can guess your response…“What is bankers rounding? I’ve never heard of it!”
Using the traditional method, if .5 always rounds up, it creates a bias over time. Bankers rounding is a method that seeks to remove that bias.
With bankers rounding, the rules are that .5 rounds to the closest even number. Therefore, the results are:
- 64.5 rounds to 64
- 69.5 rounds to 70
This is not a new concept; bankers rounding is also the default method used in VBA.
Which is right?
So, you might be wondering which is correct. Which one should you use?
The problem is that neither is right nor wrong; they are just different ways of rounding.
When is the difference an issue?
Bankers rounding removes the bias from always rounding up, so many consider it more accurate. But how much of a difference does it really make?
For illustration, if rounding a large sample of positive numbers between 1.0 – 100.0 (with 1 decimal place) to 0 decimal places:
- traditional rounding creates an upward bias of approx. 1%
- bankers rounding has a bias of approx. 0%.
However, over the same sample, bankers rounding creates a bias towards even numbers by 10%, which traditional rounding does not.
Therefore, reducing one bias creates another. So, which is best really comes down to context and significance of the potential difference.
The biggest issue is when we cease to look at large sample sizes and consider individuals. In our scenario at the start, the two methods were the difference between somebody gaining access to university. Therefore, the decision to use Excel or Power Query to calculate results could be a decision that changes the course of a person’s life.
Changing the rounding methodology in Excel or Power Query
So far, we have seen Excel and Power Query’s default behavior. But we are not stuck with those defaults; we can use traditional rounding in Power Query and bankers rounding in Excel.
Calculate bankers rounding in Excel
Using the same data as our original example. We can simulate bankers rounding in Excel with a few formulas.
The formula in Cell E3 is:
This formula provides the same result as bankers rounding.
Calculate traditional rounding in Power Query
Power Query’s round transformation has optional parameters to control the rounding type.
Adding the RoundingMode.AwayFromZero parameter into the Number.Round function forces the round type to be equivalent to traditional rounding.
You can find a list of Power Query’s other rounding methods here: https://docs.microsoft.com/en-us/powerquery-m/roundingmode-type
Default rounding options in Power Query and Excel are different. Excel uses traditional rounding, while Power Query uses bankers rounding. Both methods create a form of bias. Therefore, the best option is dependent on your context.
Over a large sample, the bias may be insignificant. But individually, it can alter the results and have a significant impact.
Excel and Power Query can both calculate on either basis if programmed to do so. Therefore, being aware of the difference and the potential impact is the most crucial part. Now that you are aware of the difference, you can choose the correct basis for your scenario.
- Why are Excel formulas not calculating?
- Excel can calculate the wrong results: WARNING
- Common Power Query errors & how to fix them
After posting, Bill Jelen and Celia Alves pointed out to me that this goes even deeper, and gets even more complex. Check out their resources here:
- Helpful Secrets about ROUNDing in Power Query – Bill Jelen (Mr Excel)
- Rounding in Power Query – The default rounding mode and the binary-decimal conversion issue in Excel Celia Alvez (Solve & Excel)
If you want to know more about the mathematics of methods, check out this Wikipedia article: https://en.wikipedia.org/wiki/Rounding
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
4 thoughts on “Excel rounding vs Power Query rounding: WARNING They are different”
YEah – this might be surprice for many of people
But strange is that in “wizard mode” there is no option to select rounding mode. It might be!
I think that would be the best option, then the user can decide. Even if the don’t understand all the options, it will still be their decision.
This is an excellent tip! It’s like understanding how Trim (VBA) and TRIM (worksheet) behave differently. Mid (VBA) and MID (worksheet) is another case with some subtle difference.
These types of differences are frustrating. However, once they are there it is almost impossible to change it. They can’t risk changing the result of something that is already relied on by somebody.