Doing Statistical Analysis in Excel

1
Doing Statistical Analysisin Excel

• Mark J. Kittleson, PhD, FAAHBkittle_at_siu.edu

2
A very simple analysisone can do is to suma
column of numbers.In this case, letssum the
numbers fromC2 to C11.So, highlight C12,then
click this icon.Then hit enter
Note that this line hasthe commands of summation
3
The sum weight of all of these menis a total of
2,255pounds.
4
If one wants an averageheight of these
illustriousmen, then click the sum icon,then in
the line barclick /x where xis the number
of subjects you have. In thiscase it is 10.
5
Based on this actionthe average height (in
inches) is 70.9.
6
If one didnt want todo a cumulative weightbut
rather the averageof the 10 individualshighligh
t C13, click the sum iconand /10
7
There are more sophisicatedstatistics one can
do. First,pull down the tool menuand drag
down to data analysis
8
This is the menu that comes up. There are
various statisticsthat one can run. One of the
most common is descriptivestatistics. Scroll
down, highlight it and hit OK.
9
You will now get this box. It willask you what
set of data do youwant to analyze. Click this
box
10
Now taking your cursor, highlight the datayou
want analyzed. In this example, we areanalyzing
data from B2 to B11. After highlightingdata,
click that red/blue button again.
11
Youll come back to this boxnow click OK
12
On a separate sheet you willhave this
information appear.For example, the mean is
70.9the median is 70.5 the mode is 75.
13
Another common statistics is correlations.
Rememberthat correlations can only be run with
continuousvariables. Again, pull down the
tools menu andhighlight data analysis.
Youll get this box. Click correlation, then
hit OK
14
Again, click the red/blue box.then highlight all
of thedata you want to correlate. In this case
we aresee if the items in column B(2-11)
correlate to itemsin column C (2-11). Then
click OK
15
This is what you obtain. Thecorrelation is
-.23whichmeans that the taller you are,the
more likely you will weigh less
16
This was created by Kele Ding,MD, PhD, now at
Idaho State University
Another very useful statistic is the Chi
Square.This can be used for both nominal and
ordinaldata. Again, an Excel file has been
createdspecifically to run a Chi Square test.
17
Lets add two more variablesones political
persuasion and whetherthey like ice cream.
Lets copy andpaste them into the chi square
program.
18
Theretheyve been addedAfter you have them in,
youcan click chi square test
19
Here are the resultsthe criticalitem is the
probability issuetypically anything less than
.05 isconsidered significant
20
Another key statistic is the t test. The t
testis a way to determine differences between
two groupsof scores. In columns D and E we have
two groups of scores. Lets see if theres a
significantdifference.
21
First, pull down the tool menuthen click
t-test pairedsample for meansthen click OK.
22
First selectvariable 1were using IQage 20
(D2-11)versus IQ Age 40(E2-11). Firstclick
this box
23
Highlight the cellsin this case D2-D11.Then
click the red/blue box
24
Now click the variable 2 red/bluebox
25
For variable two, highlightcells
E2-E11thenclick the red/blue box
26
Now click OKyouare now seeing if theIQs at Age
20 aresignificantly different than when they
are 40.
27
A lot of data is foundheremost
importantly Look at the probabilityof one
tailit is .17far above theacceptable .05. So
what wecan conclude is that thereis not a
significant differencein IQs between the two age
groups
28
The next statistic is the ANOVAthis is for
seeing the difference in means in three or more
groups
29
Lets see if theres a differencein IQ among the
three groups Select Anovasingle factorthen
click OK.
30
(No Transcript)
31
(No Transcript)
32
As with the t-testthese are the resultsone is
comparing the averages IQ of 20, 40 and60 (92.7,
77.8, 73.6 respectively). TheANOVA revealed no
significant difference.
33
The final statistic is a simple rank and
percentile
34
Once this box comes up, click the red/blue
buttonand highlight what you want to
analyzethenclick OK.
35
What you have now is aranking of the scores
Click