Using MS Excel in teaching Design of Experiment

Abstract

Choosing right software to teach an applied statistical course is very crucial. When one teaches applied statistical courses to statistics students s/he needs to use an interactive software. Instructors should communicate with the software rather getting all the statistical results by clicking tabs. The mean reason is that the instructor can simultaneously teach the techniques and the data analysis. Perhaps the most straightforward option is to use the free software R but it is not easy for average students to use R. In this paper, I will share the experience of using MS Excel in teaching Design of Experiment. I find this experience as a success one.

Full text

Using MS Excel in teaching Design of Experiment
MOHAMMAD SALEHI
Mathematics, Statistics, and Physics
Qatar University
Doha, PO Box 2713
QATAR
salehi@qu.edu.qa
Abstract: - Choosing right software to teach an applied statistical course is very crucial. When one teaches
applied statistical courses to statistics students s/he needs to use an interactive software. Instructors should
communicate with the software rather getting all the statistical results by clicking tabs. The mean reason is that
the instructor can simultaneously teach the techniques and the data analysis. Perhaps the most straightforward
option is to use the free software R but it is not easy for average students to use R. In this paper, I will share the
experience of using MS Excel in teaching Design of Experiment. I find this experience as a success one.
Key-Words: - Interactive teaching approach; Statistical package; Problem-oriented; Technique-oriented.
1 Introduction
The first challenge of teaching an applied statistics
course is to choose appropriate Statistical
Packages. Instructors should solve "problem-
oriented“ and "technique-oriented” exercises [1].
For “problem-oriented” exercises those software
with clickable interfaces like SPSS and Minitab are
very useful as student does not require extensive
learning and training of them. The software
provides the required output with few clicks and
students can focus on solving the problem and
interpret the output . However teaching a statistical
course to statistics students, instructor ought to
present “technique-oriented” exercises in order to
teach the technique and review some important
concepts. Even though using artificial data sets were
discouraged for many reasons but for the first year
courses you may prepare a simple artificial data set
and use calculator to teach and clarify the technique
[5]. However, it is very difficult if not impossible to
use only calculator to teach techniques in a more
advance courses like Design of Experiment course.
An option is to use R [6] which is reasonably good
software to be used for teaching data analysis and
techniques simultaneously. Moreover, it is a free
software and designed for teaching. Personally, I
like the software very much for many its advantages
particularly for its interactive nature. However, it
requires extensive learning and training with some
programming skills [3]. R is taught in a course
called Statistical Packages (STAT371) in Statistics
program, Qatar University. However, not all
students have passed this course when they take
Design of Experiment (STAT332). Even those
students who passed the course they are reluctant to
use R.
In Spring 2016, I was supposed to teach STAT381
(Categorical data analysis) and I posted its syllabus
on Blackboard at the night of the first class. In the
syllabus, I mentioned that we are going to use R.
Right after posting it, students dropped the course
except one! My conjecture was that their favorite
software is not R. Like many universities, we
should have one more extra contact hour for each
applied statistics course in which a TA works on R
so students feel comfortable to use it. But this
opportunity is not available in our program.
However, our students are usually familiar with
Excel either from High School or Foundation
program of Qatar University so that they are very
comfortable to use Excel. This motivated me to find
the possibility of using Excel in this course. In the
first step, I did a literature survey on the subject. As
I expected there are quiet number of literatures in
which the advantage and disadvantage of using
Excel in the first year level statistical courses were
discussed. Goos and Leemans (2004) [2] discussed
using Excel to teach optimal design of experiments
in which he presented an interactive teaching
approach to use Matrix operations in Excel for
teaching optimal design. I did also Google-searched
to find out whether some colleagues use Excel in
teaching Design of Experiment but I could not find
any. I decided to use Excel beside SPSS and
Minitab in teaching. I will present an Example
solved in Excel to demonstrate how plausible using
Excel in Design of Experiment course.
Mohammad Salehi
International Journal of Education and Learning Systems
http://iaras.org/iaras/journals/ijels
ISSN: 2367-8933
93
Volume 1, 2016

2
A
i
a
U
p
t
w
s
a
M
e
w
T
b
2
P
i
v
E
a
u
r
t
F
r
2
Used F
e
A
n importa
n
i
nteractively
a
pproaches
U
sing MS
E
p
roblem a
n
t
echniques a
n
w
e may u
s
s
peed up th
e
a
nalysis par
t
M
ontgomer
y
e
xamples us
w
ith details.
T
he most fr
e
b
elow.
2
.1 PivotT
P
ivotTable
c
i
ncluding fr
e
v
ariances, e
t
E
xcel. The
d
a
re in a sta
u
seful to ge
t
r
eplications.
t
ab.
F
igure 1.
P
r
ed boxes.
e
atures o
n
t advantage
b
e used in c
offered by
E
xcel helps
n
d its solu
t
nd methods
s
e a softwa
r
e
teaching
a
t
of the co
y
(2013) [4]
a
ing MS Ex
Used Exc
e
e
quent used
able
c
reates Mul
t
e
quencies, s
u
t
c.
P
ivotTa
b
d
ata set files
ck format
s
t
sums of t
h
Figure 1 s
h
P
ivotTable
i
f MS Ex
c
of MS Exc
e
lass, avoids
some stati
s
students to
t
ion. After
by an inter
r
e like SPS
S
a
nd emphas
i
urse. Ou
a
nd I solve
d
cels during
e
l features
w
features w
i
t
i-ways tabl
e
u
mmations,
b
le is on th
e
provided b
y
s
o that
P
iv
o
h
e factor l
e
h
ows
P
ivotT
a
i
n Insert ta
b
c
el
e
l is that it
c
the "black b
o
s
tical softw
a
understand
teaching
active soft
w
S
or Minita
b
i
ze on the
d
r
text boo
k
d
the most o
f
the class t
i
w
ere very
fe
i
ll be prese
n
e
of functi
o
the means,
e
Insert ta
b
y
the text b
o
o
tTable is
v
e
vels as wel
l
a
ble and In
s
b
is shown
c
an
o
x"
a
re.
the
the
w
are
b
to
d
ata
k
is
f
its
i
me
fe
w.
n
ted
o
ns,
the
b
in
o
ok
v
ery
l
as
s
ert
by
2.
2
T
o
pr
o
op
s
u
m
w
h
E
x
V
a
3
A
s
ta
u
ex
a
pr
e
co
m
cl
a
W
e
ex
a
en
g
wi
l
te
m
at
ha
s
m
a
no
wi
l
te
m
H
o
de
v
en
g
te
m
te
m
us
e
le
v
de
s
pl
a
ra
n
ob
fo
r
T
h
W
th
e



2
Simple
fu
o
compute
f
o
blems one
n
erations, t
h
m
mation, S
U
h
ich can eas
i
x
cel. I also
a
lues in the
A
Exampl
e
s
it is menti
u
ght metho
d
a
mple to s
h
e
sented in t
h
m
putation o
f
a
ss.
e
present E
x
a
mple of a f
a
g
ineer is des
i
l
l be subje
c
m
perature. Th
e
this point is
t
s
three po
s
a
nufactured a
n
control over
l
l encounter,
m
perature wil
l
o
wever, temp
e
v
elopment la
b
g
ineer decide
m
perature le
v
m
perature lev
e
environme
n
v
els, this de
s
s
ign. Four b
a
a
te material a
n
n
dom order.
served batt
e
r
mat.
h
e general m
o
W
e are intere
e
equality
o

:


:

fu
nctions
f
ormulas in
n
eeds to use
h
e square/s
q
U
M(), for a
ly
b
e done i
n
used
F
.DI
S
A
NOVA ta
b
e
s
oned, Exce
l
d
s in the c
o
h
ow that a
p
h
e class wit
h
f
formulas i
n
x
ample 5.3.
o
a
ctorial desig
n
i
gning a batt
e
c
ted to so
m
e
only design
t
he plate mat
s
sible choic
e
n
d is shipped
t
the temperat
u
and he k
n
l
probably af
f
e
rature can
b
b
oratory for
t
s to test all t
h
v
els-15, 70,
els are consi
s
n
t. Because t
h
s
ign is some
t
a
tteries are te
s
n
d temperatu
r
The exper
i
e
ry life dat
a
o
del is
sted in test
i
o
f row treat
m



0



0
the experi
m
the four b
a
q
uare root o
p
row, colu
m
n
a nice inte
r
S
T.RT for
c
b
les.
l
was used
f
o
urse. I wi
l
p
ractical ex
a
h
details of
n
a reasona
b
o
f the text b
o
n
involving
t
e
ry for use i
n
m
e extreme
parameter th
a
erial for the
b
e
s. When t
h
t
o the field, t
h
u
re extremes
n
ows from e
x
f
ect the effect
i
b
e controlled
t
he purposes
h
ree plate m
a
and 125°F-
s
tent with th
e
h
ere are two
f
t
imes called
s
ted at each
c
r
e, and all 36
i
ment and
a
are given
i
ng hypoth
e
m
ent effect
s
m
ental desi
g
a
sic arithmet
i
p
erations a
n
m
n or a matr
i
r
active way
i
c
omputing
P
f
or almost
a
l
l present
a
a
mple can
b
methods a
n
b
le time in t
h
o
ok [4].
As
a
t
wo factors,
a
n
a device th
a
variations
i
a
t he can sele
c
b
attery, and
h
h
e device
h
e engineer h
a
that the devi
c
x
perience th
a
i
ve battery li
f
in the produ
c
of a test. "T
h
a
terials at thr
e
b
ecause the
s
e
product en
d
f
actors at thr
e
a 3
2
factori
a
c
ombination
o
tests are run
i
the resulti
n
in Figure
e
ses about
s
, say
(1)
g
n
i
c
n
d
i
x
i
n
P
-
a
ll
a
n
b
e
n
d
h
e
a
n
a
n
a
t
i
n
c
t
h
e
is
a
s
c
e
a
t
f
e.
c
t
h
e
e
e
s
e
d
-
e
e
a
l
o
f
i
n
n
g
1
Mohammad Salehi
International Journal of Education and Learning Systems
http://iaras.org/iaras/journals/ijels
ISSN: 2367-8933
94
Volume 1, 2016

a

W
a
E
T
o
T
A
t
t
i
c
F
S
R
1
2
3
(
b
G
a
nd the equa
l



:





:



W
e are also
a
nd column
t


:



:



To test (1),
(
Which can
b
E
xcel and th
e
T
able 1
: Us
i
o
ver the row
s
T
his is repro
d
A
t this stag
e
t
ables of ob
s
t
he gray cell
s
i
s better to
p
c
hanges.
F
i
g
ure 1
: V
a
umof
L
Column
15
R
owLa
b
123
1
130 74 155
2
150 159 188
3
138 168 110
b
lank)
G
randT
o
418 401 453
l
ity of colu
m



0




0
interested i
n
t
reatments i
n
0







(
2) and (3)
w
b
e done qui
c
e
result will
b
i
ng PivotTa
b
s
, the colum
n
d
uction of T
e
, to copy
s
ervations a
n
s
, to other c
e
p
aste them a
s
a
lues (V) Pas
15Total 70
41
180 539 34
126 623 136
1
160 576 174
1
466 1738 344
3
m
n treatment

0

n
determini
n
n
teract.





0
w
e should co
m
c
kly by usin
g
b
e as follow
s
b
le to comp
u
n
s factors a
n
able 5.4 of t
h
and paste
T
n
d the rows
t
e
lls, Table
2
s
Values to
a
te option.
70Total 12
5
234
1
80 40 75 229 2
0
1
06 122 115 479 2
5
1
50 120 139 583 9
6
3
3
6
282 329 1291 14
1
effects, say
(2)
n
g whether
r



(3)
m
pute
g
P
ivotTabl
e
s
u
te summati
o
n
d replicatio
n
h
e text book
.
T
able 1 in
t
t
otals, value
s
2
(a) and (b)
.
a
void unwa
n
5
125Total
(
1
234 (b
0
82 70 58 230
5
58 70 45 198
6
82 104 60 342
1
222 244 163 770
r
ow
e
in
o
ns
n
.
.
t
wo
s
in
.
It
n
ted
T
a
Ro
w
1
2
3
Total
T
T
h
m
e
W
re
q
T
h
th
e
th
e
ju
s
fr
e
di
v
th
e
S
SE
fu
n
(
GrandTo
t
lank)
998
1300
1501
3799
a
ble 2:
a)
Copy
Table
1
15
w
12
1
130 74 1
5
2
150 159 1
8
3
138 168 11
s418 401 4
5
b)
Copy
Table
15
T
otal To
539 2
623 4
576 5
h
en, using
e
thods pre
d
W
e can no
w
q
uired in A
N
h
ey are com
p
e
arithmetic
o
e
table of A
N
s
t the sum
e
edoms, th
e
v
iding the fi
r
e
forth colu
m
SE
. The las
t
n
ction “=F.
D
and Paste
1
.
70
341
5
5 180 34
8
8
8 126 136 1
0
0 160 174 1
5
5
3 466 344 3
3
and Paste
1.
70
tal 125
Tota
l
29 23
0
79 198
83 34
2
square o
p
d
uce square
s
w
calculat
e
N
OVA tab
l
p
uted just us
i
o
perations.
W
N
OVA, Ta
b
of squares,
e
next col
u
r
st column
b
m
n is divid
i
t
column is
D
IST.RT(F0
,
of obser
v
1
23 4
8
040 75
0
6 122 115
5
0 120 139
3
6 282 329 1
of the ro
w
l
Grand
Total
0
998
1300
2
1501
p
ration, ^
2
s
of Table
2
e
the sum
l
e from Ta
b
i
ng row, col
u
W
e are now
b
le 3. The f
i
the second
u
mn is
b
y the secon
d
i
ng the thi
r
the P-valu
e
,
Df,DFE)” i
s
v
ations fro
m
25
1 23
20 82 70
25 58 70
96 82 104
41 222 244
1
w
totals fro
m
2
, and dr
a
2
(a) and (b
)
of squar
e
b
le 2.
u
mn sum()a
n
able to crea
t
i
rst column
i
is degree
o
produced
b
d
column a
n
r
d column
b
e
s for whi
c
s
used. Wh
e
m
4
58 998
45 1300
60 1501
1
63 3799
m
a
g
)
.
e
s
n
d
t
e
i
s
o
f
b
y
n
d
b
y
c
h
e
n
Mohammad Salehi
International Journal of Education and Learning Systems
http://iaras.org/iaras/journals/ijels
ISSN: 2367-8933
95
Volume 1, 2016

one uses those software with clickable interfaces
the P-values are provided out of the dark so that
students gradually forget its concept and they only
know that H0 is rejected if it is small but we can
emphasize that it is probability of observing the
current observed data under H0 using Excel. We can
also have a flashback on the F distribution structure.
Table 3: The ANOVA table.
SS DF. MS F
0
P-value
SSA= 10683.7 2 5341.8611 7.911 0.001976
SSB= 39118.7 2 19559.361 28.97 1.91E-07
SSAB= 9613.78 4 2403.4444 3.56 0.018611
SSE= 18230.8 27 675.21296
SST= 77647 35 2218.4849
Solving this example took less than 30 minutes in
the class while I could review all computations and
concepts. More importantly, students were
following the steps very well.
4 Students feedback
Students were quite comfortable with Excel and
they expressed their appreciation for using Excel.
To test their level of learning through Excel I gave
the following question in the final exam and
restricted them to use Excel only. The question is
an Analysis of Covariance problem from Chapter 15
of the test book.
Question 4
An engineer is studying the effect of cutting speed on the
rate of metal removal in a machining operation.
However, the rate of metal removal is also related to the
hardness of the test specimen. Five observations are
taken at each cutting speed. The amount of metal
removed (y) and the hardness of the specimen (x) are
shown in the following table. Analyze the data using and
analysis of covariance. Use =0.05.
Cutting Speed (rpm)
1000 1000 1200 1200 1400 1400
y x y x y x
8 60 52 105 58 115
30 80 34 80 22 72
38 90 5 60 13 64
17 65 14 65 32 81
28 76 25 73 20 70
a) Write appropriate model indicating the
response, the factor, and the covariate variables.
b) Compute all necessary S.., T.. and E..’s, and
test the hypothesis that there is no treatment
effect at level 0.05.
c) Test the hypothesis that the covariate variable
(the hardness) has no effect at 0.05.
I had 23 students in this course. The mean mark for
the final exam was 23.12 out of 35 while the mean
score for Question 4 was 7.1 out of 10. This means
that the mean percentage for Question 4 was 71%
while for that of final exam was 66%. This shows
that student did slightly better in Question 4. An
example of a student’s Excel sheet in the final exam
is giving in Figure 3.
4 Conclusion
I used Excel effectively in teaching Design of
Experiment for statistics students and I found it
useful to teach “technique-oriented” exercises in the
class. Many example were taught using Excel. It
was mainly used as a calculator. Our students were
comfortable to use it and express their preference of
using Excel. However, I do not claim Excel is the
best software to be used but we significantly spent
less time to teach it compare to a statistical packages
like R that requires extensive learning and training
with some programming skills. Nevertheless, it can
be a good solution when no TA available to assist
instructor for teaching statistical package and
students have problem to use a programmable
software.
References:
[1] Chatfield C. Teaching a Course in Applied
Statistics, Journal of the Royal Statistical
Society. Series C (Applied Statistics), Vol. 31,
No. 3, 1982 pp. 272-289
[2] Goos P. and Leemans H., Journal of Statistics
Education Vol.12, No 3, 2004),
www.amstat.org/publications/jse/v12n3/goos.ht
ml.
[3] Montgomery, D. C. Design and Analysis of
Experiments, 8th edition, John Wiley & Sons,
New York, 2013.
[4] Paura L., Arhipova I. Advantages and
Disadvantages of Professional and Free
Software for Teaching Statistics, Information
Mohammad Salehi
International Journal of Education and Learning Systems
http://iaras.org/iaras/journals/ijels
ISSN: 2367-8933
96
Volume 1, 2016

Technology and Management Science, Vol. 15,
2012, pp 9-14.
[5] Singer J. D. and Willett J. B, Improving the
Teaching of Applied Statistics: Putting the Data
Back into Data Analysis, The American
Statistician, Vol. 44, No.3, 1990, 224-230.
[6] The R Project for Statistical Computing.
[Online]. Available: http://www.R-project.org/
Figure 2: Exel sheet of results for the Example.
Mohammad Salehi
International Journal of Education and Learning Systems
http://iaras.org/iaras/journals/ijels
ISSN: 2367-8933
97
Volume 1, 2016

Figure3: Student’s example in final exam.
Mohammad Salehi
International Journal of Education and Learning Systems
http://iaras.org/iaras/journals/ijels
ISSN: 2367-8933
98
Volume 1, 2016