Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Excel rounding vs Power Query rounding: WARNING They are different

Excel Rounding vs PQ Rounding

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


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.

Exam Scores

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:

Excel Round Function applied to scores

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…

Applying PQ round transformation from ribbon

Set the decimal places to zero.

Round to zero decimal places

Finally, close and load the values back to Excel.

Rounding in Excel and Power Query - The differences

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.

Alternative Rounding Methods - PQ and Excel

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.

Power Query parameter to force traditional rounding

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.

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:

If you want to know more about the mathematics of methods, check out this Wikipedia article: https://en.wikipedia.org/wiki/Rounding



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
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

  1. Henn Sarv says:

    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!

    • Excel Off The Grid says:

      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.

  2. David N says:

    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.

    • Excel Off The Grid says:

      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.

Leave a Reply

Your email address will not be published.