Data Analysis Histogram Tool seems to be treating bin/class ranges inconsistently and coming up with incorrect frequencies

I'm using the Data Analysis Toolpack on Excel 2019 (64-bit, in Windows 10) to create Histograms and frequency tables. I've used this in teaching for years and just discovered this issue on a particular set of example data.

I have a list of 30 data values, all with 3 decimal places, to be put into a frequency table/histogram with the following classes or bin ranges:

2.470-2.479

2.480-2.489

2.490-2.499

2.500-2.509

2.510-2.519

I open the histogram tool from data analysis, input the 30 data values in the Input Range, and in the Bin Range, I insert the upper class limits of all those classes in cells like so:

2.579

2.589

2.499

2.509

2.519

The results I get are incorrect though. For example, there are 2 values in the data set of 2.509, which are counted not in the range 2.500-2.509 but in 2.510-2.519. This was confusing on its own, but I thought maybe Excel was treating the bin range numbers as non-inclusive maximums and somehow I just never noticed that before. BUT the frequency table is also placing a data value of 2.489 in the 2.480-2.489 range, which is completely inconsistent!

Any idea what's happening here? I can't find any specific documentation about how Excel actually uses the numbers input into the Bin Range to create the frequency counts. But now I'm worried I've been teaching students to create these incorrectly for years!

Thank you!

Paige

I can provide the original data if needed but I can't seem to paste it in here.

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

joeu2004

Probably the "floating-point" issue due to the fact that most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel uses internally. And the fact that Excel formats only up to 15 significant digits; consequently, some infinitesimal differences are not visible.

If any of the values with decimal fractions are calculated, be sure to __explicitly_round__ to 3 decimal places. Formatting to 3 decimal places is not sufficient (unless you enable "Precision As Displayed", which I would discourage).

For a more complete explanation, upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com. You might prefer onedrive.live.com because it uses the same login as answers.microsoft.com.

In any case, test the download URL first, being careful to log out of all websites that share the same login, in order to ensure that anyone else can access the file anonymously.

-----

PS.... You wrote:

``I have a list of 30 data values [...] with the following classes or bin ranges:

2.470-2.479

2.480-2.489

[....]

I insert the upper class limits of all those classes in cells like so:

2.579

2.589``

Presumably, 579 and 589 (sic) are posting typos.

But you might double-check your Excel file to be sure they are indeed 479 and 489.

Data Analysis Histogram Tool seems to be treating bin/class ranges inconsistently and coming up with incorrect frequencies

Replies (3)

Question Info

Data Analysis Histogram Tool seems to be treating bin/class ranges inconsistently and coming up with incorrect frequencies

Replies (3) 

Question Info

Replies (3)