Michael asks why when you add up three cells with 1/3, you don't get 0.99? Episode 1116 takes a look at how to solve this.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Question sent in today by Michael is a kind of sort of related to rounding.
I'm going to format all of these cells here as currency with a couple of decimal places, so we just choose Currency, alright, and Michael puts in a formula here.
=1/3, and you see, of course, we get $0.33 all the way down, adds it up, and wonders why he's not getting 99 cents!
OK well, I mean, Excel is really storing that, not as .33, but as .3333333, and if you want this to add up to $0.99, I have a couple of options for you.
Option number one, let's copy this over, option number one here is: do not enter a dollar /3, but actually use the ROUND function, so I want ROUND(1/3,2).
In other words, two digits after the decimal place, then we get $0.99.
Or another option to go: is to go into Excel Options, and we'll go to the Advanced tab, and then scroll all the way down here for: When calculating this workbook: Set precision as displayed.
That means if we're only showing two decimal places, only calculate it out to two decimal places.
Big warning there, data will permanently lose accuracy in the whole workbook, be careful before you do this, it's going to cut things off everywhere.
Click OK, click OK, and now we get 33, 33, 33, is 99.
So a couple of options there, I'm always afraid of the Set precision as displayed, might be easier to use the ROUND function in order to work that out, a couple of different ways to go.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Question sent in today by Michael is a kind of sort of related to rounding.
I'm going to format all of these cells here as currency with a couple of decimal places, so we just choose Currency, alright, and Michael puts in a formula here.
=1/3, and you see, of course, we get $0.33 all the way down, adds it up, and wonders why he's not getting 99 cents!
OK well, I mean, Excel is really storing that, not as .33, but as .3333333, and if you want this to add up to $0.99, I have a couple of options for you.
Option number one, let's copy this over, option number one here is: do not enter a dollar /3, but actually use the ROUND function, so I want ROUND(1/3,2).
In other words, two digits after the decimal place, then we get $0.99.
Or another option to go: is to go into Excel Options, and we'll go to the Advanced tab, and then scroll all the way down here for: When calculating this workbook: Set precision as displayed.
That means if we're only showing two decimal places, only calculate it out to two decimal places.
Big warning there, data will permanently lose accuracy in the whole workbook, be careful before you do this, it's going to cut things off everywhere.
Click OK, click OK, and now we get 33, 33, 33, is 99.
So a couple of options there, I'm always afraid of the Set precision as displayed, might be easier to use the ROUND function in order to work that out, a couple of different ways to go.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
