Microsoft KB Archive/23944

{| = Excel: Arranging Data for a Histogram Chart =
 * width="100%"|

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.
 * }