Statistics

1
DESCRIPTIVE STATISTICS USING EXCEL
This set of instructions will help us learn how to do descriptive statistics using
Excel. We will also learn how to construct a frequency table on Excel. Let’s get
started.
1) This is the example we have been using in the lecture notes.
Heights of 50 male students (cm)
182, 181, 177, 176, 175, 177, 171, 186, 165, 187,
165, 176, 158, 150, 169, 153, 169, 186, 178, 181,
166, 173, 187, 182, 167, 176, 179, 167, 171, 186,
174, 165, 159, 169, 175, 188, 171, 162, 171, 163,
181, 169, 180, 177, 165, 178, 170, 176, 173, 162
very difficult to take this in
need to summarise to help us turn these raw data into useful information
To support decision making, answer questions, make a point etc.
– preferably graphically
Go to course web page on BLACKBOARD; find and copy the file “Height.xlsx”
to your user space (or your USB memory stick). Then, start Excel and open the
file “Height.xlsx”.
2) Naming a range of data. It makes life easier if we name the data range so
that we do not need to select or type in the cell references each time we write a
formula. How?
First, select or highlight the range of data (i.e. select cells A2 to A51). Then, select
Formulas – Name Manager – Define Name to get the following New Name
dialogue box:
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

2
3) Let’s find out the sample size (i.e. n). Type n in cell C2. Type the formula
=COUNT(Data) in cell D2 and click enter (you should see 50 in D2).
4) Type mean in cell C3 and type the formula =average(data) in cell D3 (you
should get 172.88). Did you notice the functions are not case sensitive (i.e. it
doesn’t matter whether you type the formula in capital or small letters)?
5) Type standard deviation in cell C4 and type the formula =stdev(data) in cell
D4 (you should get 8.879 – 3 d.p.).
6) Type mode in cell C5 and type the formula =mode(data) in cell D5 (you
should get 176). NOTE that Excel is unable to report if there is more than one
mode in the data set. To find out if there are more than one mode, type the
formula =count(mode.mult(data)) in cell E4 (you should notice that there are 4
modes). To find the multiple modes, first select cells E5 to E8 and then type the
formula =mode.mult(data) and then press Ctrl+Shift+Enter at the same time.
Then you should see the four modes in cells E5, E6, E7 and E8.
7) Type min in cell C6 and type the formula =min(data) in cell D6. (=150)
Note that the “Refers to:” box at the bottom of the screen
includes the name of the sheet and refers to the data
range we want to name. Click OK to complete
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

3
8) Type max in cell C7 and type the formula =max(data) in cell D7. (=188)
9) Type range in cell C8 and type the formula =max(data) – min(data) (or =D7
– D6) in cell D8. Notice that there is not a single formula for range. (=38)
10) Type median in cell C9 and type the formula =median(data) in cell D9
(173.5).
11) We can also provide a five-point (number) data summary (min, first quartile,
median, third quartile, and max) of any data set using Excel’s QUARTILE
command. Type min; first quartile; median; third quartile; and max in cells
C10 to C14 respectively. Then, type the formulae =Quartile(data,0);
=Quartile.exc(data,1); =Quartile.exc(data, 2); =Quartile.exc(data,3); and
=Quartile(data,4) in cells D10 to D14 respectively (150; 166.75; 173.5; 179.25;
188).
12) Since we have found the first and third quartiles, we can calculate the interquartile range (IQR), and hence the inner and outer fences. Remember IQR is
equal to Third Quartile – First Quartile. Inner fences are from (First Quartile –
1.5*IQR) to (Third Quartile + 1.5*IQR). Outer fences are from (First Quartile –
3*IQR) to (Third Quartile + 3*IQR). Type appropriate formulas to calculate IQR,
inner and outer fences. Are there any mild or serious outliers?
13) To find the skewness of data, type in the formula =skew(data). The result (-
0.368) suggests we may assume data is normally distributed (close enough to
zero) but slightly skewed (negatively) to the left.
14) So far we have learned all the necessary commands for descriptive
statistics. You may wonder how one could remember all these formulas. Don’t
worry! Now, if you are not sure what the formula for a particular statistic is, you
can do the following: Suppose you cannot remember the formula for Quartiles.
Select an empty cell (say D22). Under Formulas, select Insert Function button,
Skewness: -3 ……………… 0 ………………+3
to left symmetrical to right
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

4
i.e. . From resulting dialogue box, select the category “Statistical” as shown
below:
In each category, functions are listed in alphabetical order. Since we are
interested in Quartiles, the function is likely to start with Q. Browse down and
once you locate the command, click OK and you will be presented with a new
dialogue box where you need to specify the data range (i.e. array of data) and
quart (i.e. the quartile you want to calculate). Notice that the Function Arguments
dialogue box explains and tells you what values you should enter as shown
below:
15) We have now learnt (hopefully!) how to do descriptive statistics by entering
appropriate formulas. Fortunately, there is a shortcut to do the descriptive
statistics using Excel’s built-in “Descriptive Statistics” function. Do the following:
Under Data tab, select Data Analysis – Descriptive Statistics***[see next
page] to get the following dialogue box.
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

5
Enter the following into appropriate boxes:
Input range: Data
Select Output Range and enter an empty cell reference (e.g. $D$43) into the
box opposite to Output Range.
Select Summary statistics and click OK.
*** You should normally be able to see Data Analysis under the Data menu.
However, if you can’t find Data Analysis, do the following:
The Analysis ToolPak is a Microsoft Office Excel add-in program that is
available when you install Microsoft Office or Excel. To use it in Excel, however,
you need to load it first.
1. Click on File menu in Excel and then click Options (at the bottom of the
drop down menu).
2. In Excel Options dialogue box, click Add-Ins (towards the bottom of the list
on the left), and then in the Manage box, select Excel Add-ins.
3. Click Go.
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

6
4. In the Add-Ins available box, select the Analysis ToolPak check box, and
then click OK.
Tip: If Analysis ToolPak is not listed in the Add-Ins available box, click
Browse to locate it.
If you get prompted that the Analysis ToolPak is not currently installed on
your computer, click Yes to install it.
5. After you load the Analysis ToolPak, the Data Analysis command is
available in the Analysis group on the Data tab.
Now, we’ll turn our attention to construct a frequency table on Excel.
CONSTRUCTING A FREQUENCY TABLE ON EXCEL
1) Deciding on class width. Remember class width is the distance between two
consecutive lower class or upper class limits. Determining a class width is an art
more than a science. We discussed about how to determine a class width in the
lecture. Suppose we decided that the class width should be 5. Enter 5 into cell
H2.
2) Lower class limit of the first class must be less than equal to the minimum
data value in the data set. So let’s start with 143. Type 143 in cell H6.
3) Enter =H6+$H$2 in cell H7 to calculate the lower class limit of the second
class. Copy this formula down till H17.
4) Enter =H7-1 in cell I6 to determine the upper class limit of the first class. This
formula is to ensure that the two consecutive classes do not overlap with each
other.
5) Now, enter =I6+$H$2 in cell I7 and copy it down to determine the upper class
limits of the other classes (Note that we assumed that the data are discrete
although the variable, height, is actually a continuous variable).
6) Class boundary is the dividing line (or fence) between the two consecutive
classes. It is usually set up half way through between the upper class limit of the
preceding class and the lower class limit of the succeeding class. Type in the
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

7
appropriate formula to calculate the first upper class boundary and copy the
formula down.
6) Type the correct formula to find the class midpoints. Remember the class
midpoint is the half way through the lower and upper class limits of the same
class.
7) Now, we need to instruct Excel to find how many data values fall into each
class. First, Excel needs to look up the data set and record the frequency of data
values falling into the first class, the second, the third and so on. To do this, the
formula =index(frequency(data_range, the upper class limits), the first class)
must be entered. Now, you work out what should be the cell references for the
expressions in bold and italics. Remember when and where to use absolute or
relative references.
8) Type the necessary formulas to complete the rest of the frequency table. Make
sure the figures in the last two columns are expressed in percentages (%) rather
than in decimal points.
9) Create a histogram by plotting the class midpoints (x axis) against the
frequencies (y axis) using a Column Chart. (Click on Insert – Column – Under
2D Column select the first chart type (top left) – Clustered column). Your
histogram should look like this:
0
2
4
6
8
10
12
14
145 150 155 160 165 170 175 180 185 190 195
Frequency
Midpoints
Heights – Histogram
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

8
10) Create an Ogive to answer the question “How many students are taller than
175cm?” (Plot the upper class boundaries against the cumulative frequency,
chart type: Line – select upper left).
11) Create an Ogive to answer the question “What percentage of students are
taller than 175cm?” (Plot the upper class boundaries against the cumulative
relative frequency, chart type: Line – select upper left).
0
5
10
15
20
25
30
35
40
45
50
147.5 152.5 157.5 162.5 167.5 172.5 177.5 182.5 187.5 192.5 197.5
Cumulative frequency
Upper class boundaries
Ogive
0%
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
147.5 152.5 157.5 162.5 167.5 172.5 177.5 182.5 187.5 192.5 197.5
Cumulative Relative Frequency
Upper class boundaries
Ogive
UTSA College of Business Descriptive Statistics Using Excel
Maho Sonmez MS1023 Business Statistics w/Comp. Apps. I

9
12) Create a frequency polygon to see and comment on the shape of distribution
of the data set. (Plot the class midpoints against the frequencies, chart type: Line
– select upper left).
13) Select the frequency polygon you created in (12). Right click, select Change
Chart Type and select chart type Area (select the first option).
0
2
4
6
8
10
12
14
145 150 155 160 165 170 175 180 185 190 195
Frequency
Mid points
Heights – Frequency Polygon
0
2
4
6
8
10
12
14
145 150 155 160 165 170 175 180 185 190 195
Frequency
Mid points
Heights – Area