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.

KBCategory: kbtool

KBSubcategory:
Additional reference words: 5.00 4.00 4.00a 7.00 frequency cumulative
distribution
Keywords : kbtool
Version : 5.00 5.00c 7.00 7.00a | 5.00 5.0
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Microsoft KB Archive/118445

Contents