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!
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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question send in, the person has a spreadsheet and you can see numbers here in column F and column N.
The number in column N is 5000, is a sum.
He totaled the numbers, and he wants to check to see if the two numbers match.
So, we entered a simple formula here that says, =N702= F701, and even though we can see both numbers are $5,000.
Excel is saying they don't match.
So, we want to see what's going on.
I change this formula to values and when we change it to values, you'll see that up here in the formula bar, instead of being 5,000, it is 5,000 point 0... 2.
This is what's known as the floating point arithmetic error.
It's a situation where Excel is thinking in binary and a simple number to us.
You know, such as 1 or 10, might be a repeating number in binary and so, we end up with this tiny decimal fraction.
Well, a couple of things we could do the first thing we can do is, use the Round function.
We can always check to see is if the, =ROUND(N702,2)=F701, and of course that will show up is true.
I'm gonna do undo, to go back to the original formula to check.
There's one very obscure setting that I want to tell you about, but you have to be careful when you use it, and you have to understand exactly what's going to happen.
You'll see that I have numbers here in F, that have two decimal points, numbers here an N, that have two decimal points.
If I go to tools and then option, then go to the Calculation tab and there's a setting down here called Precision as displayed.
Now, this is a very, let's say dangerous or drastic step.
When you check this box, Excel is going to go through the worksheet and change the numbers.
Actually, cutting off decimals, if they're beyond what's displayed.
So, we click Precision as displayed, click OK It says, Data will permanently lose accuracy, click OK, and now it's actually converted that 5000 point 0... 2, to 5000.
Now, you have to be careful what else in the workbook could have had either, Dates and Times where, just the date was displayed.
We lose the times or maybe, you had currency and you had formatted it to not show the cents.
So, you would have lost those cents.
So, it's very easy to really have some problems after you use this setting.
I would recommend using the Round function to see if they're equal.
But, if you desperately want to get rid of all the floating point errors within the worksheet, you could use Tools, Options, Precision as displayed.
Hey, there you have it.
Thanks for stopping by.
See you next time for another net cast from MrExcel.
I'm Bill Jelen.
Today we have a question send in, the person has a spreadsheet and you can see numbers here in column F and column N.
The number in column N is 5000, is a sum.
He totaled the numbers, and he wants to check to see if the two numbers match.
So, we entered a simple formula here that says, =N702= F701, and even though we can see both numbers are $5,000.
Excel is saying they don't match.
So, we want to see what's going on.
I change this formula to values and when we change it to values, you'll see that up here in the formula bar, instead of being 5,000, it is 5,000 point 0... 2.
This is what's known as the floating point arithmetic error.
It's a situation where Excel is thinking in binary and a simple number to us.
You know, such as 1 or 10, might be a repeating number in binary and so, we end up with this tiny decimal fraction.
Well, a couple of things we could do the first thing we can do is, use the Round function.
We can always check to see is if the, =ROUND(N702,2)=F701, and of course that will show up is true.
I'm gonna do undo, to go back to the original formula to check.
There's one very obscure setting that I want to tell you about, but you have to be careful when you use it, and you have to understand exactly what's going to happen.
You'll see that I have numbers here in F, that have two decimal points, numbers here an N, that have two decimal points.
If I go to tools and then option, then go to the Calculation tab and there's a setting down here called Precision as displayed.
Now, this is a very, let's say dangerous or drastic step.
When you check this box, Excel is going to go through the worksheet and change the numbers.
Actually, cutting off decimals, if they're beyond what's displayed.
So, we click Precision as displayed, click OK It says, Data will permanently lose accuracy, click OK, and now it's actually converted that 5000 point 0... 2, to 5000.
Now, you have to be careful what else in the workbook could have had either, Dates and Times where, just the date was displayed.
We lose the times or maybe, you had currency and you had formatted it to not show the cents.
So, you would have lost those cents.
So, it's very easy to really have some problems after you use this setting.
I would recommend using the Round function to see if they're equal.
But, if you desperately want to get rid of all the floating point errors within the worksheet, you could use Tools, Options, Precision as displayed.
Hey, there you have it.
Thanks for stopping by.
See you next time for another net cast from MrExcel.
