Docstoc

Histogram

Document Sample
Histogram Powered By Docstoc
					  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
e. Added statistical
       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