Excel: Arranging Data for a Histogram Chart

Last reviewed: November 30, 1994
Article ID: Q23944

SUMMARY

In Microsoft Excel, to chart a column of numbers as a histogram (for example, to chart the number of occurrences from 0 to 10, 10 to 20, and so on) you must find the number of occurrences within the different ranges, as follows:

Select a cell on the spreadsheet separate from where your actual data lies.
To find the number of occurrences between a lower limit and an upper limit, type in the following formula and press COMMAND+ENTER (CONTROL+SHIFT+ENTER if using Excel for Windows) to enter it as an array formula:
```
      =SUM(IF(range>=lower_limit,IF(range<=upper_limit,1)))
```
"range" is the array reference to the cells that contain your data. "lower_limit" and "upper_limit" are the bounds.
Enter the same formula into adjacent cells, substituting appropriate bounds for "lower_limit" and "upper_limit".

The values that these formulas return can then be plotted in an Excel chart to represent a histogram.

MORE INFORMATION

To plot a count of the number of values between 10-20, 21-30, 31-40, and 41-50 from cells A1:A500, the following formulas can be used:

   B1: =SUM(IF(A1:A500>=10,IF(A1:A500<=20,1)))
   B2: =SUM(IF(A1:A500>=21,IF(A1:A500<=30,1)))
   B3: =SUM(IF(A1:A500>=31,IF(A1:A500<=40,1)))
   B4: =SUM(IF(A1:A500>=41,IF(A1:A500<=50,1)))

Select B1:B4, choose New from the File menu and select Chart. The appropriate values are plotted as a histogram chart.

KBCategory: kbusage
KBSubcategory:
Additional words: crosstabs distribution 2.20 2.2 3.00 3.0

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.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.