Intro to Excel & statistics - central limit theorem & monte carlo simulation

background [prelab assignment (24 points) due at the begining of lab; reminder: as appropriate, use in-text (see subsequent sections) citation and reference list (see subsequent sections); have a copy of your prelab & a printout of this lab handout available, so you can refer to it during the lab]

1. you will be using MS Excel to simulate rolling a dice (many times). what is a simulation versus a Monte Carlo simulation ?

2. describe how to use MS Excel to find / generate

i. mean

ii. SD

iii. histogram (alternatively, use "frequency" & "bar chart" features)

iv. simulate rolling a 6-sided dice

as appropriate, include sketches / screen shots to clarify your response.

3. describe the ___; clearly differentiate between the terms, 'sample' versus 'sample of means' in your response.

a. central limit theoreom (with regards to the normal distribution)

b. standard error of the mean (SEM); relate to the standard deviation.

4. what probability distribution describes rolling a dice ? what is the formula for the mean and standard deviation of this distribution ?


introduction to the use of Excel & statistics

1. simulate rolling 6-, 8-, and 10- sided dice

2. simulate rolling a 6- sided dice, 120 times, to examine the validity / plausibility of the Central Limit Theoreom by comparing the histogram of the distribution of the means consisting of a sample size of 1, 2, 9, versus 36.

3. compare the mean of rolling a 6- versus 8- versus 10- sided dice using a sample size of 4 versus 16

fulfill various aspects of the NGSS science & engineering standards; including computational thinking / (cs) computer science


computer with MS Excel

partial template: dice roll Excel simulation - xlsm (enable VBA macro)

lab report prepared using Excel & Word (or equivalent)

methods / data analysis

refer to the purpose of the lab and the content of the lab report in determining what to do in the lab

content of lab report [30 points; group lab report; up to 2 students / group]

6-sided dice simulation [15 points]

histograms: distribution of the mean of sample size of 1, 2, 9, or 36 of rolling a 6-sided dice; include a description of the data; use 120 simulated runs (or dice rolls) for each sample size

qualitatively, does the preceding histograms support the central limit thereom ? consequence ?

hint regarding consequence: both the t-test and 1-anova assume that the samples comes from a normal distribution.

calculate the theoretical value of the SEM (for sample size of 2, 9, & 36), SD, and mean for rolling a 6-sided dice; show your work; include appropriate citation for formula. qualitatively, compare to experimental value.

statistical analysis (use links in below resource section); include interpreation / implication of statistics test results, as well as, data entered into the statistical tests; include data source / description. [5 points]

1-anova / Tukey test: compare the value of the mean in the 6-, 8-, & 10- sided dice simulation. run test, twice - using sample size of 4 versus sample size of 16

describe / list five NGSS science & engineering practices that correlates to an aspect of this laboratory activity; provide an example in the lab / lab report that meet these practices [10 points]


excel tutorial to generate random numbers for rolling dice

mathematica (accessible only on computers in the classroom).

caveats in using spreadsheet software (optional); e.g.

On the accuracy of statistical procedures in Mircosoft Excel 2010. 2011.

Beware of Excel Histograms. (blog; 2012)

Spreadsheets in the Cloud - not ready yet. 2013


The risks of using spreadsheets for statistical analysis. 2013. (IBM promo)

Using Excel for statistical analysis 2010

Statistical analysis using Microsoft Excel 2008

Using Excel for Statistical Data Analysis - Caveats 2007

counter - point: value of using Excel (optional); e.g.

Teaching Fundamental Skills in Microsoft Excel to First-Year Students in Quantitative Analysis. 2015

Review of Advanced Excel for Scientific Data Analysis; 3rd ed. 2012