Descriptive Statistics – Excel Tools Instruction
Histogram
- A histogram consists of contiguous (adjoining) boxes. It has both a horizontal axis and a vertical axis. The horizontal axis is labeled with what the data represents (for instance, distance from your home to school). The vertical axis is labeled either frequency or relative frequency (or percent frequency or probability). The graph will have the same shape with either label. The histogram (like the stemplot) can give you the shape of the data, the center, and the spread of the data.
Histogram in Excel
The following data are the heights (in inches to the nearest half inch) of 100 male semiprofessional soccer players. The heights are continuous data, since height is measured.
60; 60.5; 61; 61; 61.5; 63.5; 63.5; 63.5; 64; 64; 64; 64; 64; 64; 64; 64.5; 64.5; 64.5; 64.5; 64.5; 64.5; 64.5; 64.5 66; 66; 66; 66; 66; 66; 66; 66; 66; 66; 66.5; 66.5; 66.5; 66.5; 66.5; 66.5; 66.5; 66.5; 66.5; 66.5; 66.5; 67; 67; 67; 67; 67; 67; 67; 67; 67; 67; 67; 67; 67.5; 67.5; 67.5; 67.5; 67.5; 67.5; 67.5; 68; 68; 69; 69; 69; 69; 69; 69; 69; 69; 69; 69; 69.5; 69.5; 69.5; 69.5; 69.5; 70; 70; 70; 70; 70; 70; 70.5; 70.5; 70.5; 71; 71; 71; 72; 72; 72; 72.5; 72.5; 73; 73.5; 74
- Enter the data into column A. Create bins Range into column C
- Click Data, Data Analysis, Histogram and OK
- Specify Input Range ($A$1:$A$101), Bin Range ($C$1:$C$9), and Output Range ($E$1)
- Click Labels, Chart Output and OK
- Make changes for the Histogram (i.e. delete Frequency, More on the right side)
- Click on one blue rectangle, right click, click Format Data Series
- To change the ‘gap width’, click on symbol ı under Gap Width slide line, hold and slide it to 0%
- Click on the Histogram and icon + to make changes
- Change Axis Titles
To change the bin range on the histogram table, change the values in the X-Axis data.
Line Chart
- A line chart is often used to represent a set of data values in which a quantity varies with time. These graphs are useful for finding trends. That is, finding a general pattern in data sets including temperature, sales, employment, company profit or cost over a period of time.
Line chart in Excel
- Enter the data (Year and Annual) into column A, B
- Highlight the columns of data, click Insert, Line Chart
- Click “More Line Charts”
- Choose the graph with a single line.
- Click on Y-axis data, right click, Format Axis
- Change Minimum and Maximum values which suit your data best, click Enter
- Click on the new bar graph and icon + to make Axis title changes
Mean, Median & Mode
- Mean: a number that measures the central tendency of the data; a common name for mean is ‘average’.
- Median: a number that separates ordered data into halves; half the values are the same number or smaller than the median and half the values are the same number or larger than the median. The median may or may not be part of the data.
- Mode: the value that appears most frequently in a set of data.
Mean, Median & Mode in Excel (For