Microsoft KB Archive/118445

= Microsoft Knowledge Base =

XL: Histogram Tool Gives Different Results Than in Excel 4.0
Last reviewed: March 27, 1997

Article ID: Q118445

5.00 5.00c 7.00 7.00a | 5.00 5.00a

WINDOWS              | MACINTOSH kbtool

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for Windows 95, versions 7.0, 7.0a

SYMPTOMS
In the versions of Microsoft Excel listed above, the Histogram tool in the Analysis ToolPak add-in macro will return different results that it does in Microsoft Excel versions 4.x.

CAUSE
The Histogram tool in Microsoft Excel versions 5.0 and 7.0 puts the input values into different bins than the Histogram tool in Microsoft Excel 4.0. Also, the Histogram tool in Microsoft Excel 4.0 does not recognize input values smaller than the first bin number, while the Histogram tool in Microsoft Excel versions 5.0 and 7.0 do recognize these values.

STATUS
This change in behavior is by design. The behavior of the Histogram tool was changed from version 4.0 to be consistent with the bin distribution of the FREQUENCY function.

MORE INFORMATION
In Microsoft Excel 4.0, everything greater than or equal to the bin number, but less than the next bin number, is included in the bin, while in Microsoft Excel versions 5.0 and 7.0, a number is counted as being in a particular bin if it is equal to or less than the bin number down to the last bin.

Also, the Histogram tool in Microsoft Excel versions 5.0 and 7.0 count all values below the first bin value and all values above the last bin value. The Histogram tool in Microsoft Excel version 4.0 does not count any values lower than the first bin value, but it does count all values above the highest bin value.

Example
Given the following Input values,

A1: 8.59 A2: 10.0 A3: 10.2 A4: 10.4 A5: 10.6 A6: 10.8 A7: 11.0 A8: 11.2 A9: 11.4 A10:11.6 A11:11.8 A12:12.0 A13:14.0

and the following bin values:

B1: 9 B2: 10 B3: 11 B4: 12 In Microsoft Excel version 4.0 the results would be the following

Bin         Frequency 9          0 10          5 11          5 12          2

while in Microsoft Excel versions 5.0 and 7.0, the results would be:

Bin        Frequency 9          1 10          1 11          5 12          5 More        1 The Histogram Tool in Microsoft Excel version 4.0 doesn't return values for bin 9, 5 for 10, 5 for 11 and two for 12. However, the histogram tool in Microsoft Excel version 5.0, gives 1 for 9, 1 for 10, 5 for 11, and 5 for 12. This is because Microsoft Excel version 4.0 counts everything in Bin 9 as being equal to or greater than 9 up to the next bin, which is 10.

Also, you will notice that the Histogram tool in Microsoft Excel version 4.0 returns only four values. The reason for the difference in results is that Microsoft Excel versions 5.0 and 7.0 count the value 8.59 for the first value that is returned, while the Histogram tool for Microsoft Excel version 4.0 does not count the inputs below the first bin value, but it does count the numbers greater than the last bin value.