Excel - Fixing Floating Point Errors in Excel: Avoid Rounding Issues - Episode 564

Microsoft Excel Tutorial: Fixing Floating Point Errors in Excel: Avoid Rounding Issues.

Welcome back to the MrExcel netcast where we tackle all your Excel questions and problems. I'm Bill Jelen and today we have a question sent in by one of our viewers. They have a spreadsheet with numbers in columns F and N, and they want to check if the sum in column N matches the number in column F. However, even though the numbers appear to be the same, Excel is showing that they don't match. So, let's dive in and see what's going on.

Upon further investigation, we can see that the number in column N is actually 5000.02 instead of just 5000. This is what's known as a floating point arithmetic error, where Excel is thinking in binary and a simple number to us may actually be a repeating number in binary, resulting in a tiny decimal fraction. So, how do we fix this issue?

One solution is to use the ROUND function to round the numbers to a specific number of decimal places. In this case, we can use =ROUND(N702,2)=F701 to check if the numbers match, and it will show up as true. However, there is another option that you need to be careful with. In the Calculation tab under Tools and Options, there is a setting called Precision as displayed. When checked, Excel will go through the worksheet and change the numbers, cutting off any decimals beyond what is displayed. This can be a drastic step and may result in losing accuracy in other areas of the worksheet, such as dates, times, or currency formatting.

So, while the Precision as displayed option may be a quick fix, I would recommend using the ROUND function to check for equality. But, if you really want to get rid of all the floating point errors in your worksheet, you can use the Precision as displayed option. Just be sure to double check for any potential issues that may arise from this setting. Thanks for tuning in to this netcast from MrExcel. Don't forget to subscribe and hit that notification bell for more Excel tips and tricks. 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) Introduction
(00:19) Question about mismatched numbers
(00:29) Explanation of floating point arithmetic error
(01:01) Solution 1: Use the Round function
(01:25) Solution 2: Change Precision as displayed setting
(02:30) 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:
Converting numbers in Excel to match displayed values
Excel formula to check if two numbers match
Fixing rounding errors in Excel
Floating point arithmetic error in Excel
How to avoid floating point errors in Excel
How to change decimal precision in Excel
How to use the Round function in Excel
Losing accuracy when using Precision as displayed in Excel
Potential issues with Precision as displayed setting in Excel
Precision as displayed option in Excel
Recommended approach for handling floating point errors in Excel
Tips for maintaining accuracy in Excel calculations

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



Have you ever had an Excel calculation show up with a tiny fraction like .0000000002 at the end of the number? This prevents Excel from seeing if two numbers are equal or not. Episode 564 will show you two methods for dealing with this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!