Skip to main content
Statistics LibreTexts

5.1 Probability for Means using Excel

  • Page ID
    22454
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    Definition: Central Limit Theorem

    The Central Limit Theorem states that regardless of the underlying distribution, the probability of the average greater than or less than a number is Normally distributed, provided the sample size is large enough.


    Large enough can be a wide range of values.  If the underlying distribution is Normal, then the sample does not matter.  If the distribution is very different from the normal distribution, a sample size of at least 30 is required.


    The Normal distribution for the average has the same mean as the original distribution, and the new standard deviation (also called standard error) is the standard deviation divided by the square root of the sample size.

    Individual Probability variable x with mean µ and standard deviation is \(\sigma\).

    \(\bar{x}\)  ~ N(µ, \(\sigma\)/\(\sqrt{n}\)) where n is the sample size.

    Example \(\PageIndex{1}\)

    Suppose the individual aptitude score of students in a gifted program is from a Uniform distribution with a minimum of 10 points and a  maximum of 25 points.  The school took a sample of 40 aptitude scores to compute probabilities about the average student's score.  

    a) What is the distribution of the average aptitude score for the students? What is the mean and standard error of the average aptitude score?

    First, compute the mean of the individual probability.

    Mean of the Uniform distribution is equal to 

    µ = \(\frac{(Minimum + Maximum)}{2}\) = \(\frac{(10+25)}{2}\) = \(\frac{35}{2}\) = 17.5

    Excel formula =(10+25)/2

    Include rounding in Excel formula.  (Round to 4 decimal places)

    =round((10+25)/2, 4)

    ANSWER

    17.5

    Standard Deviation of the Uniform distribution is equal to 

    \(\sigma\) = \(\frac{(Maximum - Minimum)}{\sqrt{12}}\)

    Excel Formula:  =(25-10)/sqrt(12)

    Include rounding in Excel formula. (Round to 4 decimal places)

    =round((25-10)/sqrt(12), 4)

    ANSWER
    4.3301

    If you want to round it to two places change 4 to 2.

    The sample size is large enough, (n > 30) to conclude the average aptitude score of students in the program follow the Normal distribution with a mean of 17.5 and standard deviation (standard error) of \(\sigma\)/\(\sqrt{n}\) = \(\frac{4.3301}{\sqrt{40}}\) = 0.6846531969.

    Excel Formula:  =4.33013/sqrt(40)

    Include rounding in Excel formula. (Round to 4 decimal places)

    =round(4.3301/sqrt(40), 4)

    ANSWER
    0.6847

    b) P(\(\bar{x}\) >16.0), the probability that the average score is greater than 16.

    P(\(\bar{x}\) > 16.0) = 1 - P(\(bar{x}\) < 16.0)

    Excel Formula: =1 - norm.dist(16, 17.5, 0.6847, TRUE)

    Include rounding in Excel formula.  (Round to 4 decimal places)

    = round(1 - norm.dist(16, 17.5, 0.6847, TRUE), 4)

    ANSWER
    0.9858

    c) P(\(\bar{x}\) < 15.5), the probability that the average score is less or no more than 15.5.

    Excel Formula: =norm.dist(15.5, 17.5, 0.6847, TRUE)

    Include rounding in Excel formula.  (Round to 4 decimal places)

    =round(norm.dist(15.5, 17.5, 0.6847, TRUE), 4)

    ANSWER
    0.0017

     

    d) P(16 < \(\bar{x}\) <18.5), the probability that the average score is between 16 and 18.5.

    Excel formula: =norm.dist(18.5, 17.5, 0.6847, TRUE) - norm.dist(16, 17.5, 0.6847, TRUE)

    Include rounding in Excel formula.  (Round to 4 decimal places)

    ANSWER

    0.9137

    e) What is the 70th percentile?

    To find the 70th percentile use the Excel formula

    =norm.inv(0.70, 17.5,0.6847)

    17.85905703

    Include rounding in Excel formula. (Round to 2 decimal places)

    =round(norm.inv(0.70, 17.5, 0.6847), 2)

    ANSWER

    17.86

    f) What is the Upper 10th percentile?

    To find the upper 10th percentile you are actually finding 100-10 = 90th percentile.

    Excel formula

    =norm.inv(0.90, 17.5, 0.6847)

    18.37747836

    Include rounding in Excel formula. (Round to 2 decimal places)

    =round(norm.inv(0.90, 17.5, 0.6847),2)

    ANSWER

    18.38

    Interactive Element

    Example \(\PageIndex{2}\)

    Using the Excel Spreadsheet provided

    Suppose the individual aptitude score of students in a gifted program is from a Uniform distribution with a minimum of 10 points and a  maximum of 25 points.  The school took a sample of 40 aptitude scores to compute probabilities about the average student's score.

    Open the Excel Spreadsheet provided 

    a) What is the distribution of the average aptitude score for the students? What is the mean and standard error of the average aptitude score?

    Click on the Uniform Distribution tab in the Excel spreadsheet. 

    Enter 10 in cell B1. 

    Enter 25 in cell B2.

    The Mean is in cell B4, 17.5.

    The standard deviation of the individual distribution is 4.33013.

    Click on the Normal Prob Dist Mean tab.  

    Enter 17.5 in cell B1.

    Enter 4.3301 in cell B2.

    Enter 40 in cell B7.

    The standard error is in cell B8, 0.6847.

    b) P(\(\bar{x}\) >16.0), the probability that the average score is greater than 16.

    Enter 16.0 in cell B5.

    The probability is in cell B9, 0.9858.

    c) P(\(\bar{x}\) < 15.5), the probability that the average score is less or no more than 15.5.

    Enter 15.5 in cell B5.

    The probability is in cell B10, 0.0017.

    d) P(16 < \(\bar{x}\) <18.5), the probability that the average score is between 16 and 18.5.

    Enter 16 in cell B5.

    Enter 18.5 in cell B6.

    The probability is in cell B11, 0.9137.

    e) What is the 70th percentile?

    Enter 0.70 in cell E4.

    The 70th percentile is in cell E5, 17.86 rounded to 2 decimal place.

    f) What is the Upper 10th percentile?

    The Upper 10th percentile is the 100 - 10 = 90th percentile.

    Enter 0.90 in cell E1.

    The Upper 10 percentile is 18.3775 or 18.38

     
    Interactive Element


    5.1 Probability for Means using Excel is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?