Excel for Statistical Analysis Guide
Translated by
ScribdTranslationsExcel for Statistical Analysis Guide
Translated by
ScribdTranslationsStatistical analysis using Excel
PRESENTATION
Many times, Biostatistics seems complicated precisely because of the complexity of
mathematical calculations involved. However, it is possible to use computational resources to
to lessen this difficulty.
In this Learning Unit, you will become familiar with some resources available in
Excel for statistical analysis. You will learn how to create distribution tables.
frequencies, you will know which graphs are suitable for each type of variable and still you will
familiarize yourself with some statistical tests.
Good studies.
At the end of this Learning Unit, you should present the following learnings:
Describe the statistical data using spreadsheets created in Excel.
• Build tables and graphs using Excel.
• List the parametric and non-parametric statistical tests using Excel.
CHALLENGE
Within statistical analysis, two very important concepts are measures of position and
or of variability. To conduct analyses of quantitative variables, it is possible to calculate, by
through the use of Excel, the measures of position (mean, mode, and median) and measures of variability
(variance and standard deviation).
Interactive content available on the teaching platform!
Calculate the average height and weight of the customers and the standard deviation to know the variability in
I return from the average of your data.
INFOGRAPHIC
Many statistical analyses can be done using Excel. One can start with the basics, such as the
generation of tables and graphs, up to inferential statistics, with hypothesis testing.
See in the infographic the step-by-step calculation of the average using this software, being the
Mean is one of the measures of position to summarize numerical data.
BOOK CONTENT
Statistical data is collected through a survey of information about certain
issues. However, only with the survey, the statistical data in isolation are
little use; it is important that it is possible to analyze and compare this data in order to achieve a
totality of information. Through the Excel tool, it is possible to combine the survey of
data with your analysis.
In the chapter Statistical analysis using Excel, from the work Bioestatistics, you will get to know
some tools available in Excel to perform some statistical calculations. You
you will also learn basic tips on how to perform descriptive statistics and parametric tests
non-parametric with the use of Excel.
Good reading.
BIOSTATISTICS
Juliane Silveira Freire da Silva
Statistical analysis
using Excel
Learning objectives
At the end of this text, you should present the following learnings:
Describe the statistical data through elaborated spreadsheets.
no Excel.
Build tables and graphs using Excel.
List the parametric and non-parametric statistical tests using
to Excel.
Introduction
It is through a survey of information regarding certain
in the subjects that the statistical data is collected. However, the
The collection of statistical data in isolation is of little use.
In this way, it is important that it is possible to analyze and compare these
data, in order to achieve a completeness of information, what can be done
through the Excel tool, where it is possible to unite the survey
of data with its analysis.
In this chapter, you will learn how to set up a database.
statistician in Excel, how to organize the data correctly to
subsequent statistical analyses, as well as it will build tables and charts
with the help of Excel and will learn about the main tests that can be
carried out in this software.
Statistical data
When we gather information or do some type of
observation and we noted this result, we are collecting statistical data.
When, for example, we fill out an evaluation form for a new student
in a gym, we are collecting some information about this individual
2 Statistical analysis using Excel
duo, and we do this for every new student who starts the activity. The set
this information is what we call statistical data.
But if we leave this data noted on the registration forms, we do not
we will have all the information that these data can provide us. We need,
So, tabulate this data and analyze it with the records of other students;
if this is not done, we will only have isolated records and we will not be able to
we will not be able to verify any trends or make comparisons, such as,
example, analyze the evolution of the students of this academy. To build
a database, we need to organize the variables so that each
the column of the spreadsheet is a variable, and each row of that spreadsheet is a
sample or population unit.
Consider a form containing variables relevant to the physical anamnesis and
some behavioral variables for filling new students
from this gym, considering the quantitative variables of age, weight, height
and the qualitative variables sex, history of pain, diagnosed disease pre-
-existing. Note, in Table 1, that the variables are arranged in each of
of the columns, and each line contains the information of a patient. In the first
line, then, the student is 32 years old, male, weighs 72 kg and is 1.70 m tall,
tem histórico de dores e não tem doença pré-existente.
Table 1. Data from the cards
Age Sex Peso Altura History of pains Disease
32 Male 72 170 Yes No
18 Female 55 165 Yes No
25 Female 60 155 No No
36 Female 65 160 Yes No
40 Male 80 190 No No
44 Female 70 163 No Diabetes
52 Female 81 157 No Diabetes
19 Male 69 180 No Asma
18 Male 79 185 No No
23 Male 78 180 No No
(Continues)
Statistical analysis using Excel 3
(Continuation)
Table 1. Data from the records
Age Sex Peso Altura History of pain Disease
52 Male 90 182 Yes High blood pressure
31 Female 79 165 No No
37 Feminino 97 170 Yes No
46 Male 95 180 No No
55 Female 69 155 No No
36 Female 55 165 Yes Diabetes
23 Male 60 175 Yes No
25 Female 58 168 Yes No
47 Female 68 157 No No
The data represented this way does not provide us with much information yet.
Imagine if, instead of having this little data, we had a spreadsheet.
with 100 students: we would only have a bunch of numbers and words noted down
and we would not be able to observe any trend. However, this is the way
that the data must be organized so that we can carry out the first
analyses, which we call descriptive analyses.
According to Callegari-Jacques (2007), the description of the variables is indispensable.
as a preliminary step for the proper interpretation of the results of
an investigation, and the methodology used is part of descriptive statistics.
Descriptive analysis is one of the divisions of statistics. In this phase of analysis, it is
the first summary of the data has been made. Descriptive statistics, then, corresponds
the collection, organization, presentation, and summary of data (with diagrams and
graphs or using a summarized numerical value) (DOANE; SEWARD,
2014). Descriptive statistics can be produced in the form of tables.
de distribuição de frequências, em forma de gráficos e em forma de resumos
numerical, such as the mean and standard deviation.
For quantitative variables, we can calculate the measures of position.
(mean, mode, and median) and the measures of variability (variance and standard deviation)
standard) using Excel. For this, we only need the database,
according to Table 1, to start the analyses.
4 Statistical analysis using Excel
In Excel, we have some predefined functions. By clicking onx ,
functions appear classified by categories, and by clicking on statistics-
within the category, we find all the functions of this category.
Statistics, we will learn to use the functions predefined by Excel
for the calculation of the average, median, mode, variance, and standard deviation
standard (Figure 1).
Figure 1. Explanatory screen of Excel functions.
The average of a sample is defined by the sum of all elements,
divided by the number of elements. We carry out this calculation easily.
in Excel: we type in the destination cell “=average(num1, num2....)”, or else
we click emfxand we search for the word average, one way or another
(Figure 1). After choosing the function, simply select the data and click on
enter. Being the median the central value of an ordered set of values,
we type in the destination cell "=average(num1, num2, ...)" or we click
emfxand we search for the word median, one way or another. After
after choosing the function, just select the data and click enter. For the
Statistical analysis using Excel 5
mode, which is the most frequent value of the data distribution, we type
“=modo(núm1, núm2,...)”, selecionamos os dados e clicamos ementer. Para
the calculation of the standard deviation, which measures the variability of the data, we type
in the target cell “=stdev.p(num1, num2,…)”, or we click on emfx
and we searched for the word sample standard deviation, one way or another.
After choosing the function, just select the data and press enter. It is
it is important to emphasize that this is the function for calculating the standard deviation
from a sample.
Figure 2. Explanatory sheet calculation of the average.
Tables and charts using Excel
For the first summary of the data, we can construct the distribution tables.
of frequencies using tables for categorical data, which are the tables of
frequency distribution for qualitative variables, as exemplified
in Table 2, below.
6 Statistical analysis using Excel
Table 2. Frequency distribution for qualitative variables
Sexo Frequência %
Female 11 57.9
Male 8 42.1
Total 19 100.0
The first column of the frequency distribution table is the variable
studied, the second column results from the count of each of the responses
received in the sample, the third column is the percentage, which can be calculated
by the rule of three.
We also have the frequency distribution table by point, used
for discrete quantitative variables, as exemplified in Table 3, the
to follow.
Table 3. Frequency distribution for discrete quantitative variables
Number of days you exercise Frequency %
1 6 10.3
2 8 13.8
3 13 22.4
4 12 20.7
5 10 127,
6 7 12.1
7 2 3.4
Total 58 100.0
And, still, when we have a continuous quantitative variable and, in some
cases, as discrete quantitative ones, it is necessary that we build this
table by value intervals, as exemplified in Table 4.
Statistical analysis using Excel 7
Table 4. Frequency distribution for quantitative variables by intervals of
values
Height range Frequency %
155|—160 4 21.1
160|—165 2 10.5
165—170 4 21.1
170|—175 2 10.5
175|—180 1 5.3
180|—185 4 21,1
185—190 2 10.5
Total 19 100,0
In this representation, the symbol of the vertical bar ( | ) indicates that the number
it is contained in the range where the bar is present next to you.
A|—B - A is contained and B is not.
A—|B – A is not contained and B is contained.
A—B - A and B are not contained.
A|—|B – A and B are contained.
In all types of simple frequency distribution tables, we have
always, in the first column, the variable, in the second column, the count of the fre-
observed sequence, and in the third column, the percentage. The distribution table
of frequencies by intervals (also called frequency distribution table
frequency by classes) is not directly provided in Excel, as it first needs to be
we need to organize the intervals so that we can later create the table.
The fastest way to set up the distribution tables of freight
sequences by point and for categorical data is with the use of the table resource
Excel dynamics. In the toolbar, we click on the insert tab and sele-
we create a pivot table (see Figure 3). Excel then prompts us to select
the data, and we selected the entire spreadsheet. At this moment, Excel creates a
8 Statistical analysis using Excel
new tab in your spreadsheet, where we can produce all the tables that will be
interesting to be developed.
Com o recurso de tabelas dinâmicas podemos elaborar tabelas de distribui-
simple frequency analysis, as previously presented,
the table of categorical data and the frequency distribution table by
point. In addition to simple tables, the feature also allows for creating tables.
crosswords, as exemplified in Table 5.
Table 5. Cross table
History of pains
Sex No Yes Total
Female 6 5.0 11
Male 5 3.0 8
Total 11 8 19
Figure 3. Explanatory screen for inserting a pivot table.
Statistical analysis using Excel 9
With pivot tables, we can build our simple tables.
the crusades. When we have a frequency distribution table for
intervals, we need to first build the intervals to then,
we can generate the table. The resource of dynamic tables also allows us to
to create averages segmented by sex, for example, just like other summaries
segmented numerics.
After we have the frequency distribution tables ready, we can
build graphs for these tables.
The pivot tables bring many features, and you can learn more by watching the
video available on the following link.
[Link]
Now, our focus will be on the simplest graphs, which are part of the
descriptive statistics, the first summary we make with our database
data. There is a large number of charts available, but here, it studies-
Let's remove the basic charts.
After we have the tables ready, we can create the graphs in
Excel. Again, we go to the insert tab and this time we insert charts.
(in this window we have several graphs available).
For the frequency distribution table for categorical data, we can
use pie charts, column charts, and bar charts.
In our example for the data from the sex variable table, we can obtain
the following graph (Figure 4).
10 Statistical analysis using Excel
Figure 4. Explanatory screen for inserting graphs.
It is important to know that the pie chart is recommended for use only
for qualitative variables. For the data in our distribution table of
frequencies by points, we can create column or bar charts,
as shown in Figure 5.
Statistical analysis using Excel 11
Figure 5. Explanatory screen for inserting column charts.
The resulting bar chart would be as shown in Figure 6, after
formatted.
Figure 6. Bar chart.
The correct chart for a frequency distribution table by
intervals are the histogram, which is nothing more than a bar chart
"grudadas", with no space between them. Just make a graph of
12 Statistical analysis using Excel
columns in Excel and right-click on the columns,
go to format data series and reset the spacing between columns
(Figure 7).
Figure 7. Histogram.
In addition to these graphs, Excel also provides us with line graphs, to
when we have a quantitative variable that is accompanied by a
time period. We use the scatter plot when we have two
quantitative variables and we want to verify the correlation between one variable
dependent and an independent variable.
Parametric statistical tests
and non-parametric using Excel
When we already have the first summary of our data and have the statistics
descriptive data, many times we still want to explore the part of this
statistical inference.
We use statistical inference when, based on a sample,
we want to infer for the entire population. This is possible when we carry out
statistical tests. Statistical inference refers to generalizing results from
a sample for a population, estimate unknown parameters, reach
to conclusions and make decisions (DOANE; SEWARD, 2014).
Statistical analysis using Excel 13
Excel provides some features for us to perform statistical tests
parametric and non-parametric. We use parametric tests when the data
follow a normal or approximately normal distribution. However, the tests do not
Parametric tests are used when the data do not follow a distribution.
normal or approximately normal, or simply when we do not know
the distribution that the data follow, or, still, when the variability of
data is too high. Non-parametric tests are also suitable for
when we are analyzing qualitative variables.
Parametric tests require specific assumptions about the population, or
populations, from where the samples come. In many cases, we must admit
that populations have an approximately normal distribution shape,
that their variances are known or that it is known they are equal, or that the
samples should be independent. Since there are many situations where it is doubtful
if all necessary assumptions can be met, the statisticians elaborate
rarely alternative procedures based on less restrictive assumptions,
which come to be known as non-parametric tests (FREUND, 2006).
Excel provides us with the values of test statistics for various distributions.
sections, as well as some probabilities of known distributions. These
functions are available emfxSome examples are:
DIST.F = returns the F probability distribution;
[Link].N = returns the standard normal distribution;
[Link].N = returns the normal distribution with mean and standard deviation
-specified standards;
[Link] = returns the probability of the right tail of the dis-
chi-square distribution and provides the probability of the test statistic
of this square;
DIST.T = returns the probability of the left tail of the distribution
t-student.
We can cite the F distributions, t-student, and normal distribution as
being distributions used for parametric tests, and the chi distribution
square for non-parametric tests.
These functions presented and others available in Excel deliver values.
of probabilities, and some reveal the probabilities of the test statistics,
the known values of p (p-value).
There is another resource in Excel that needs to be enabled, but that we
provides complete parametric statistical tests. It is necessary to enable the
analysis tools in Excel supplements.
14 Statistical analysis using Excel
Learn how to enable the Excel Analysis ToolPak add-in by following the link.
[Link]
Enabling the analysis tools, we have the tests available for-
metrics: ANOVA, which is used to test more than two means; z tests, for
testing two means where the population variance is known; t-tests, for
test the mean of two samples; and t-test, for paired samples and analysis of
regression, which checks the correlation between two variables.
A study on the IQ of people aged between 25 and 45 years investigated, through
from an IQ test, a sample segmented by education level. Check if the
The QIs are the same for the three levels of instruction investigated.
The formulated hypotheses are:
null hypothesis that there is no difference in IQ across the three levels of education;
alternative hypothesis that there is a difference in IQ at least at one of the levels
of instruction.
In Excel, we start the analysis after enabling the analysis toolpak add-in.
Statistical analysis using Excel 15
As we are testing the average IQ in more than two samples (more than two
types of training), we used the ANOVA test.
The obtained results are:
Observing the analysis presented, it can be verified that the p-value is significant,
that is, less than 0.05 (significance level of the 5% test). Therefore, we can
reject the null hypothesis that there will always be the hypothesis of equality.
We concluded, then, that there is a difference in the average IQ in at least one of the
levels of training, at a significance level of 5%.
16 Statistical analysis using Excel
As we can see, Excel is a great ally in statistical analysis.
applied to biostatistics. Excel primarily assists us in obtaining
a database, so that we can carry out the analyses. These analyses will
from descriptive statistics, providing tables and graphs for all types
of variables, up to measures of position (mean, mode, median) and measures of
variability (variance, standard deviation, range) for numerical variables,
that is, qualitative variables.
In addition to descriptive analysis, we can perform parametric tests, such as,
for example, ANOVA, which compares more than two means, the t-test, for
compare two means, and the paired t-test, which tests two means
compared before and after a treatment.
CALLEGARI-JACQUES, S. [Link]í[Link] Alegre: Artmed, 2007.
DOANE, D.P.; SEWARD, L. [Link]ística Aplicada à Administração e Economia. 4. ed. Porto
Cheerful: Bookman, 2014.
FRIEND, J. E. Applied statistics: economics, administration, and accounting. 11th ed.
Porto Alegre: Bookman, –2006.
Recommended readings
LAPPONI, J. C. Statistics using Excel. 4th ed. Rio de Janeiro: Elsevier, 2005.
SCHMULLER, J. Statistical analysis with Excel: for dummies. 3rd ed. Rio de Janeiro: Alta Books,
2018.
TEACHER'S TIP
There are some features available to be enabled in Excel, including the tools
of conducting parametric statistical tests. An example of these tools is the test
ANOVA, which aims to test more than two means.
See in the Teacher's Tip how to perform an ANOVA test using Excel.
Interactive content available on the learning platform!
EXERCISES
1) Thinking about developing a series of exercises more aimed at a specific age group.
a personal trainer wants to calculate the average age of his students. To do this,
he will use Excel, with the data already entered in the spreadsheet. What would be the steps
What should he follow?
A) Type equal - type with - open parenthesis - select data from all ages -
close parentheses - press enter.
B) Type equal – type average – open parenthesis – select the data for all ages –
close the parenthesis - type equals.
C) Type equal - type average - open parentheses - select the data from all ages -
close parentheses - type equal.
D) Type average - open parenthesis - select the data for all ages - close
parentheses - type the same.
E) Type equal – type average – open parentheses – select the data for all ages –
close parentheses - press enter.
2) Wishing to verify the variability in the running time of his students, a
The physical education professional decided to evaluate his students by recording the times that
each of them ran in the 200m race. The statistic that measures the variability of the
data is the standard deviation. How would this Physical Education professional do to
calculate the standard deviation of this sample of students in Excel, after entering the data in
spreadsheet?
A) Type equal - type var.a - open parentheses - select the data of all losses of
fat - close parentheses - press enter.
B) Type the same - type medium deviation - open parentheses - select the data of all
fat loss - close parentheses - type the same.
C) Type the same - type desvpad.a - open parentheses - select the data for all losses
of fat - close parentheses - press enter.
D) Type the same - type var.a - open parentheses - select the data for all losses of
fat – close parentheses – type equal.
E) Type desvpad.p - open parenthesis - select the data of all fat losses -
close parenthesis - press enter.
3) A physical education professional wants to check if weight loss with 3 types of
protocolo seguindo dieta associada com exercícios físicos tem a mesma eficiência. Para isso,
noted the loss of 10 clients for each of the three protocols after 60 days from the start of
each of them. With this data, he performed an ANOVA test in Excel, which resulted in:
Based on this data, what can he conclude at a significance level of 5%?
A) The educator can conclude that the test is not significant, since the 3 protocols are
equivalent in terms of weight loss efficiency.
B) The educator can conclude that the test is significant, since the 3 protocols are
equivalents in terms of weight loss efficiency.
C) The educator can conclude that the test is not significant, since there is a difference in at least
less one of the protocols regarding weight loss efficiency.
D) The educator can conclude that the test is significant, since there is a difference in at least
less one of the protocols regarding weight loss efficiency.
E) The educator cannot conclude if the test is significant, since there is no evidence.
sufficient for decision-making.
4) A physical education teacher intends to present to the class council what are the
main modalities that the students from their classes reported as their favorite. The
data is in the table below:
Wishing to create a chart to represent this data, what sequence of steps for
What type of appropriate chart should be generated in Excel?
A) Now insert - charts - scatter - select data - click ok.
B) Tab insert - charts - area - select data - click ok.
C) Data tab - graphs - columns - click ok - select data.
D) Tab data – charts – pie – select data – click ok.
E) Go to Insert - Charts - Pie - Select Data - Click OK.
5) Wishing to better visualize the database of 250 students from a gym, the
the manager intends to create frequency tables for categorical data with the variables
sex, city, and type. What Excel tool should he use to build
but faster these tables?
A) Data analysis.
B) Charts.
C) Pivot table.
D) Count manually and type in Excel.
E) Page layout.
IN PRACTICE
Many times, in the practice of the professional activity, it is common for some doubts to arise, such as
for example, what is the most effective method for muscle gain or the suspicion that
Men lose weight more easily than women using the same diet, etc. It already exists.
many studies conducted in articles in the area, but it may also be convenient to use a
new technique and test it.
To do this, the professional needs to follow some steps to carry out a study in which they
You want to validate a hypothesis. See, in practice, how one of these tests works.
Interactive content available on the learning platform!
LEARN MORE
To expand your knowledge on this subject, see below the suggestions from
professor:
ANOVA and unpaired t-test in Excel
Below, see how to apply the ANOVA test and the t-test in Excel.
Interactive content available on the teaching platform!
Using Excel: average, median, mode, standard deviation, variance, maximum and minimum