Excel vs Power Query rounding: WARNING difference results | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post ★

★ About this video ★
This video shows that rounding in Excel and rounding in Power Query lead to different results.

0:00 Introduction
0:27 Scenario
2:56 Rounding methods
4:23 Which method is best?
7:30 Excel calculating bankers rounding
8:30 Power Query calculating traditional rounding
9:40 Summary

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #PowerQuery

[wayneedmondson]

Very useful information! Thanks for the thorough explanation and methods to calculate in EXCEL and PQ. Thumbs up!

[esthersuh]

Thank you for very imformative and different rounding method.

[GrainneDuggan_Excel]

Excellent explanation Mark! No excuses now to get rounding issues in Excel and PQ

[bbotzong]

One big complaint I have with Microsoft is their lack of standardization. This video is one example. Another one that comes to mind is in Access, they're called CrossTab reports, in Excel, Pivot Tables, and in Power BI, Matrix. Why three names for the same concept?? One more: in VBA you can use UCASE() function to capitalize, but in Excel you have to use UPPER() function. There should be some sort of standardization review going on in all Microsoft products to prevent these annoying discrepancies.

Nice explanation! Thanks.

[stevegouldstone]

Unless I misunderstood, your example of the solution (with MROUND etc) using Excel did not give the same result as Powerquery for the second number 75)?

[sheauwenchong]

This is unrelated. 😅
Why sometimes Excel gives us 10-20 decimal places when we just do a simple =10-9.95?
Or sometimes when we import from Table/Range, a typed-in value is ended up with 10-20 decimal places in Power Query?😢
How to avoid it?

[clivesaunders]

Just because you can, doesn't mean you should. By this I mean the MOD() function is for integers only and should not be used with decimals. Modular arithmetic relates to integers only, and I find it sad that many people use it with non-integers. If you're just looking for the decimal part of a number, try X-TRUNC(X), please