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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,349
Messages
5,675,244
Members
419,557
Latest member
razlevav

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
Top