# Histogram

Document Sample

```					  This workbook provides much the same functionality as Excel's Histogram routine in its Data Analysis
Toolbox but with the output linked to the input data and achieved without macros. A maximum of 100 classes
(bins) is allowed.

To use this workbook:
- Enter your data in stacked form in column A on the Input sheet.
- Enter the First and Last bin values in G5 and G6 and the Bin Increment in G7 on the Histogram sheet.
The Number of Classes (Bins) is calculated from those three values.
There should be no gaps or missing values in the data, i.e. no empty cells in column A's data

A frequency table and histogram are shown on the Histogram sheet, along with a Cumulative frequencies
chart.

This workbook is brought to you by Rodney Carr, Deakin University, Australia and Neville Hunt, Coventry
University, UK as part of the OATBRAN project.
Last updated 13 July 1999.
Updated Feb 2008 by Carl Witthoft, Acton, MA, just for fun. --> adjusted formulas to allow fractional bin
widths, added "Under" and "Over" bins to show how much data fell outside the chosen range. Added statistical
info: data count, mean, median, mode, and so on.

For reference, here are the named variables and their assignments
BinValues =OFFSET(Histogram!\$B\$4,0,0,NumClasses+2,1)
CumFrequencies =OFFSET(Histogram!\$D\$4,0,0,NumClasses+2,1)
Data =OFFSET(Histogram!\$A\$5,0,0,n,1)
First =Histogram!\$G\$3
Frequencies =OFFSET(Histogram!\$C\$4,0,0,NumClasses+2,1)
Increment =Histogram!\$G\$5
Last =Histogram!\$G\$4
n =COUNT(Histogram!\$A:\$A)
NumClasses =CEILING((Last-First)/Increment,1)
ta Analysis
um of 100 classes

sheet.

ve frequencies

unt, Coventry

actional bin
Output sheet for Histogram
Bin Range
Data     Bin    Frequency Cumulative %                  First Midpoint               0.5
X    Under       0       0.00%                        Last Midpoint              99.5
417      0.5       0       0.00%                           Increment                 1
424      1.5       0       0.00%                       Manual selection of Min and Max allows user to 'expand' a subse
447      2.5       0       0.00%                                                         Data max:
464      3.5       0       0.00%                                                          Data min:
468      4.5       0       0.00%                                                        Data Count:
479      5.5       0       0.00%                                                      Data Average:
480      6.5       0       0.00%                                                       Data Median:
488      7.5       0       0.00%                                                   Number of Bins:
493      8.5       0       0.00%                                                 Class (Bin) Mode:
501      9.5       0       0.00%                                         Class (Bin) value at Mode:
537     10.5       0       0.00%                                             number of mode bins:
571     11.5       0       0.00%
582     12.5       0       0.00%
601     13.5       0       0.00%
602
603
14.5
15.5
0
0
0.00%
0.00%
Histogram and Cumulative Sum
607     16.5       0       0.00%
610     17.5       0       0.00%
619     18.5       0       0.00%                       45
663     19.5       0       0.00%
40
35
Frequency

676     20.5       0       0.00%                       30
735     21.5       0       0.00%                       25
770     22.5       0       0.00%                       20
778     23.5       0       0.00%                       15
811     24.5       0       0.00%                       10
824     25.5       0       0.00%                        5
884     26.5       0       0.00%                        0
9.5
Under
4.5

29.5
19.5
14.5

24.5

34.5
39.5
44.5
49.5
888     27.5       0       0.00%
907     28.5       0       0.00%
921     29.5       0       0.00%                                                                                Bin Midpoint Value
936     30.5       0       0.00%
1009     31.5       0       0.00%
1203     32.5       0       0.00%
1310     33.5       0       0.00%
1347     34.5       0       0.00%                                                                                          Histogram
1402     35.5       0       0.00%
1665     36.5       0       0.00%                        45
1660     37.5       0       0.00%                        40
Frequency

1672     38.5       0       0.00%
35
30
1672     39.5       0       0.00%                        25
20
40.5       0       0.00%                        15
41.5       0       0.00%                        10
5
42.5       0       0.00%                         0
11.5

23.5
29.5
35.5
41.5
17.5
Under
5.5

43.5       0       0.00%
44.5       0       0.00%
45.5       0       0.00%
46.5       0       0.00%                                                                                              Bin Midpoint Value
47.5       0       0.00%
48.5    0    0.00%
49.5    0    0.00%
50.5    0    0.00%
51.5    0    0.00%
52.5    0    0.00%
53.5    0    0.00%
54.5    0    0.00%
55.5    0    0.00%
56.5    0    0.00%
57.5    0    0.00%
58.5    0    0.00%
59.5    0    0.00%
60.5    0    0.00%
61.5    0    0.00%
62.5    0    0.00%
63.5    0    0.00%
64.5    0    0.00%
65.5    0    0.00%
66.5    0    0.00%
67.5    0    0.00%
68.5    0    0.00%
69.5    0    0.00%
70.5    0    0.00%
71.5    0    0.00%
72.5    0    0.00%
73.5    0    0.00%
74.5    0    0.00%
75.5    0    0.00%
76.5    0    0.00%
77.5    0    0.00%
78.5    0    0.00%
79.5    0    0.00%
80.5    0    0.00%
81.5    0    0.00%
82.5    0    0.00%
83.5    0    0.00%
84.5    0    0.00%
85.5    0    0.00%
86.5    0    0.00%
87.5    0    0.00%
88.5    0    0.00%
89.5    0    0.00%
90.5    0    0.00%
91.5    0    0.00%
92.5    0    0.00%
93.5    0    0.00%
94.5    0    0.00%
95.5    0    0.00%
96.5    0    0.00%
97.5    0    0.00%
98.5    0    0.00%
Over   40   100.00%
<-- choose whatever range (First, Last Midpoint) you desire
<-- choose whatever range (First, Last Midpoint) you desire
<-- Increment must be >0 (unless Last < First I guess)
x allows user to 'expand' a subset of histogram as desired
1672
417
40
818.15
669.5
99
Over       <--If multiple modes, only smallest one shown
40
0

nd Cumulative Sum

120%
100%
Cumulative %

80%
60%
40%
20%
0%
Over
54.5
59.5
64.5
69.5
74.5
79.5
84.5
89.5
94.5

in Midpoint Value

Histogram
95.5
41.5
47.5
53.5
59.5

71.5
77.5
83.5
89.5
65.5

Bin Midpoint Value

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 23 posted: 12/28/2011 language: English pages: 7