Excel Assignment 3: Statistics
Microsoft Excel has numerous built-in functions for descriptive statistics as well as
probability distributions like the Normal Distribution.
1. Average(value1, value2), Quartile.Inc(value1, value2, value 3), Median(value1, value2),
Min(value1, value2), Max(value1, value2), StDev.S(value1, value2)
Many descriptive stats functions work in a similar way to the Count and Sum functions. The
Average, Median, Min, Max, and StDev.S functions gives the mean, median, minimum
value, maximum value, and sample standard deviation respectively between value1 and
value2. The Quartile.Inc function gives the quartile number value3 (with value3 being
between 1 and 4) between value1 and value2.
2. To find probabilities (or percentiles) with the Normal Distribution, let us use a for the
mean and b for the standard deviation. Then Norm.Dist(k, a, b, True) gives the
probability that a randomly selected value, x, is less than (or less than or equal to) k.
That means, using the law of complements, that finding the probability that x is greater
than (or greater than or equal to) k is 1 – Norm.Dist(k, a, b, True).
Exercises: Open a new spreadsheet in Microsoft Excel and complete the following.
1. Type the following words in given cells.
Cell Word Cell Word
B1 Value C7 Median
C1 Stats C8 Q3
C2 Count C9 Max
C3 Sum C10 Standard Dev.
C4 Mean G1 Normal Dist Q
C5 Min F2 Mean
C6 Q1 F3 Standard Dev.
2. In cell A2, type “=RandBetween(1,10)”, and drag this formula from A2 down to A26. You
have now created 25 random numbers between 1 and 10.
3. THIS IS IMPORTANT. The problem with RandBetween is that it will generate a new set of
values every time the user performs a new calculation. To keep the values we have,
highlight cells A2 to A26 (hold down the SHIFT key and tap the DOWN button to do so).
Copy these values. In cell B2, right click the mouse and select PASTE VALUES. Now the
values in the B column will stay the way we want them.
MAT 202 Quantitative Reasoning
4. In cell D2, type “=COUNT(B2:B26)”, and in cell D3, type “=Sum(B2:B26)” to get the count
and sum of the values in our data set.
5. In a similar way, in D4, type “=Average(B2:B26)” to calculate the mean. In D5, type
“=Min(B2:B26)” and in D9, type “=Max(B2:B26)” to calculate the minimum and
maximum values in our data set.
6. To find the median, in D7, type “=Median(B2:B26)”, and in D6 and D8, type
“=Quartile.Inc(B2:B26, 1)” and “=Quartile.Inc(B2:B26,3)” to find the first and third
quartiles respectively. Note, we could also use “=Quartile.Inc(B2:B26, 2)” to the find the
median.
In the future, that may be a faster method, as we could drag the formula down the B
column to find our quartiles.
7. In cell D10, find the sample standard deviation by “=StDev.S(B2:B26)”. If you need the
population standard deviation (only needed if our sample size is larger than about 30),
use “=StDev.P(value1, value2).
Normal Distribution Exercises: Suppose heights of lavender plants are normally distributed
with a height of 21” and a standard deviation of 4”. Suppose that you select a lavender
plant (randomly) in your garden.
In cells G2 and G3, type “21” and “4” respectively.
1. What’s the probability that your selected lavender plant has a height of 22” or less?
In cell F5, type “P(x leq 22)”, and in cell G5 calculate this probability.
We can calculate this by typing “=Norm.Dist(22, G2, G3, True)” to get the value
0.598706326.
2. What’s the probability that your selected lavender plant has a height greater than 22”?
In cell F6, type “P(x > 22)”, and in cell G6 calculate this probability.
We can calculate this by typing “=1 – Norm.Dist(22, G2, G3, True)” to get the value
0.401293674.
3. What’s the probability that your selected lavender plant has a height greater than 14”?
In cell F7, type “P(x > 14)”, and in cell G7 calculate this probability.
We can calculate this by typing “=1 – Norm.Dist(14, G2, G3, True)” to get the value
0.959940843.
MAT 202 Quantitative Reasoning
8. Save and submit your spreadsheet in Canvas under Excel Assignment 2.
The post Excel Assignment 3: Statistics
Microsoft Excel has numerous built-in functions f appeared first on essaynook.com.