Excel rounding vs Power Query rounding: WARNING! They are different

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.

Table of Contents

• Scenario
• Excel rounding
• Power Query rounding
• Rounding methods in Excel and Power Query
• Excel uses traditional rounding
• Power Query uses bankers rounding
• Which is right?
• When is the difference an issue?
• Changing the rounding methodology in Excel or Power Query
• Calculate bankers rounding in Excel
• Calculate traditional rounding in Power Query
• Wrap-up

Watch the video

Watch the video on YouTube.

Scenario

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.

Excel rounding

To round the results in Excel, we can enter the following in Cell D3.

=ROUND([@Score],0)

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:

=IF(MOD(C3,1)=0.5,MROUND(C3,2),ROUND(C3,0))

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

Wrap-up

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.

Related posts:

• Why are Excel formulas not calculating?
• Excel can calculate the wrong results: WARNING
• Common Power Query errors & how to fix them

Update:

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

---

What next?

Discover how you can automate your work with our Excel courses and tools.