problem with significant digits and histograms

scoutwert

New Member
Joined
Sep 15, 2006
Messages
1
I have a set of data (mass measurements from an 8th grade science lab) and the students took "Before" and "After" measurements. They calculated the difference in mass (for example, 21.45 g is before; 21.43 g is after, the difference should be -0.02 g) using Excel. They are supposed to generate a histogram - but the results are incorrect because Excel uses 15 significant digits and instead of returning a value of -0.02 g, Excel returns a value of -0.199999999999996 g. Why does this happen and how do I easily correct this problem? The histograms are turning out wrong because these minute decimal place errors are causing numbers to be placed in the incorrect bin.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
It happens because fractions (other than of the form 1/2^n) cannot be exactly represented in binary form. Use the Round function to return a value with the number of significant digits your measurement accuracy can support, and then produce the histogram(s) from the rounded values.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top