Statistics for Data Analysis Using Excel (Accredited)

Explore probability concepts from basics with coin flips and card draws, and learn probability distributions, pdf, pmf, normal, binomial, Poisson, t, chi-square, and f, to prepare for inferential statistics.

Explore fundamental statistics concepts, including population and sample definitions, statistics and parameters, means, variances and proportions, and the basics of correlation and the central limit theorem.

Understand the central limit theorem with dice examples and sampling means. See how sample means become normal as n grows, with mu_xbar = mu and sigma_xbar^2 = sigma^2/n.

demonstrates the central limit theorem using Excel with uniform and bimodal datasets, showing how sample means become normal and approximate population mean and standard deviation as the sample size grows.

Explore probability basics from 0 to 1, including classical, theoretical, and empirical approaches. Define experiments, trials, events, sample space, and simple versus complex events using coin and die outcomes.

Represent the sample space with a Venn diagram and explore union, intersection, complementary, and mutually exclusive concepts; distinguish independent and dependent outcomes using dice, coins, and a pencil.

Explore the rule of multiplication and the rule of addition in probability, including independent and dependent events, intersection and union concepts, and mutually exclusive versus not mutually exclusive cases.

Explore conditional probability, using P(A|B)=P(A∩B)/P(B) to restrict the sample space, illustrated by drawing an ace given a spade, and preview factorials and permutations and combinations.

Master factorials, permutations, and combinations, including non-negative integers, zero factorial, and formulas for nPr and nCr, with and without repetition, illustrated by books, pins, fruits, and ice cream examples.

Learn how to generate random numbers with rand and randbetween, compute factorials with fact, and apply permutation and combination calculations (with and without repetition) in Excel, including rounding results.

Explore probability distributions and how they quantify outcomes in experiments. See how discrete data use binomial and Poisson distributions, while continuous data relate to normal distributions, and coin flip examples.

Explore the binomial distribution: two outcomes, independent trials, fixed n, and constant p, with formulas and real‑world examples like defect rates and coin flips.

Learn how to compute the mean and variance of the binomial distribution using mu = np and variance = np(1-p), illustrated with ten coin flips.

Apply binomial distribution in Excel using binomdist, binomdist.range, and binom.inv to calculate probabilities for specific successes, ranges, and inverse results with practical batch quality examples.

Compare the Poisson distribution with the binomial, define lambda as the mean rate of independent events per time or space, and show how the Poisson PMF estimates event probability.

Explore how to apply the Poisson distribution in Excel using Poisson.dist, with x and mean, and cumulative options to compute exact and cumulative probabilities, illustrated by calls per hour.

Contrast probability mass function and probability density function for discrete and continuous data, and introduce CDF with Poisson, binomial, and normal distributions.

Understand the normal (Gaussian) distribution, its symmetry about the mean, and how to standardize to the standard normal distribution to find areas under the curve (68, 95, 99.73%).

Explore the standard normal distribution, defined by mu and sigma, use z-scores to standardize values, and read areas from tables for probabilities.

Learn to apply the normal distribution in Excel with functions norm.dist and norm.s.dist for pdf and cdf, and norm.inv and norm.s.inv for inverse calculations, including standardized z-values.

Explore drawing the normal distribution in Excel, compute pdf and cdf with norm.dist and norm.inv, and apply to height and call-time examples.

Explore the student's t distribution for small samples with unknown population standard deviation, comparing it to the normal distribution and highlighting thicker tails, degrees of freedom and t tests.

Explore the student’s t distribution in Excel, mastering t.dist and related functions, estimating left and right tail areas, and comparing with the normal distribution.

Explore the chi-square distribution as a continuous distribution used in goodness-of-fit and test of independence, with degrees of freedom shaping its positive values, skew, and approach to normality.

Master chi square distribution in Excel with chi square dist, dist rt, inv, and inv rt for left/right areas and plots across degrees of freedom for one-variance tests and independence.

Explore the f distribution theory, its use in anova and hypothesis testing, and its role as the ratio of two independent chi square distributions with df1 and df2.

Explore the f distribution in Excel, using two degrees of freedom and alpha 0.05, and compute left and right tail areas with f.dist, f.dist.rt, f.inv, and f.inv.rt.

Explore hypothesis testing, including statistical versus practical significance. Learn one-sample z and t tests, proportion and variance tests, two-sample tests, ANOVA, contingency tables, goodness-of-fit tests, using Excel.

define the p value as the probability of observing a test statistic as extreme as the sample under the null hypothesis, compared to the alpha level to decide rejection.

Examine type I error (alpha) and type II error (beta), their relation to significance level and power, and how sample size and effect size impact rejecting true effects.

Explore how to distinguish statistical significance from practical significance in hypothesis testing, using the p value, alpha value, and real life scenario.

Calculate the required sample size for hypothesis tests of means and proportions by balancing margin of error and a 95% confidence level, using z-scores, standard deviation, and p estimates.

Use the Excel sheet to calculate sample sizes for mean and proportion with shown formulas, input confidence level, sigma, and margin of error to yield 62 or 385.

Learn to derive point estimates from sample means and use confidence and prediction intervals to infer population means, illustrated with a 70% sample average and 66.08 to 73.92% CI.

Compute point estimates, confidence intervals, and prediction intervals using Excel formulas for the given mean, standard deviation, and sample size. Explain when to use z versus t distributions based on known or unknown population standard deviation and sample size, and distinguish confidence intervals from prediction intervals.

Analyze hypothesis testing steps for means, variances, and proportions by defining null and alternate hypotheses, selecting alpha, computing test statistics, and using critical values or p-values.

Explore one and two sample tests and ANOVA to compare means, variances, and proportions, with focus on mu = mu0 and sample size or variance conditions.

Apply one-sample z test to compare a sample mean to a population mean when sigma is known or the sample is large, with alpha 0.05 for two-tailed or one-tailed tests.

Explore one-sample z tests in Excel using z.test to assess mean volume against 150 cc, with one- and two-tailed p-values, hypotheses, and known or estimated sigma.

Use the one sample t test to assess whether a small sample mean differs from the population mean with unknown standard deviation. It uses t with n-1 degrees of freedom.

Analyze one-sample t tests in Excel when population standard deviation is unknown and the sample is small, using manual t calculations to test if the mean differs from 150 cc.

Use a one proportion test to check if the sample proportion differs from p0 using z. Assume binary data and random sampling with at least five successes and five failures.

Learn to perform a one proportion test in Excel using yes/no data, comparing observed smoking rates to a hypothesized 0.21 at 95% confidence, with two-tailed and one-tailed options.

Perform a one variance test with the chi-square distribution to compare a sample variance to a hypothesized variance, verify normal distribution and random sampling, and assess against critical values.

Learn to perform a one variance test in Excel, comparing a sample variance to a historical 4 cc squared, using chi-square with df=8, concluding no change in variance.

Compare the means of two independent samples with a two-sample z test, assuming known variances and independence, and decide using a z statistic against critical values.

Explore two sample t tests for independent data, including equal-variance and Welsh t tests, form null and alternate hypotheses, and compute t values, pooled variance, and degrees of freedom.

Explains performing a two-sample t test in Excel using the data analysis toolpak to compare two teaching methods, starting with an F test for variance equality and interpreting p-values.

Learn how the paired t test analyzes dependent data with before–after measurements, computing differences, formulating mu_d=0 vs mu_d ≠ 0, and selecting one- or two-tailed tests with t and df.

Explore the paired t test in Excel using the Data Analysis Toolpak to compare before and after scores, test a one-tailed hypothesis with alpha 0.05, and interpret the p-value.

Apply a two proportions test to compare recovery proportions between two hospitals using binary outcomes; compute p-hats, choose pooled or unpooled z, and decide at alpha 0.05.

Learn to perform a two proportions test in Excel using formulas to compare drug a and drug b, with 200/180 patients and 120/90 recoveries, concluding no significant difference.

Use a two variance test with the f distribution to compare the variances of two independent samples, testing sigma1^2 = sigma2^2 with the f statistic and one- or two-tailed decisions.

Use one-way ANOVA to compare means across three or more independent samples, using the f statistic to detect differences, with independence, normality, and equal variances assumed.

Explore an anova example comparing three machines producing volumes, compute mean squares between and within, obtain the f statistic and p value, and test the null hypothesis of equal means.

Fill in an ANOVA table from a practical three-machine example, computing between and within sums of squares, mean squares, and the F statistic to complete the analysis.

Compare three machines using one-factor ANOVA in Excel to test mean volumes. See how the F statistic and a p-value below 0.05 lead to rejecting the null.

Perform a two-factor ANOVA without replication in Excel to assess how diet type and exercise level affect weight loss over four weeks, using the Data Analysis Tool Pack.

Explore two-factor anova with replication in Excel to assess how machine and material affect tensile strength, including their interaction, using replicated measurements and Excel's data analysis tool.

Assess whether observed and expected frequencies fit a hypothesized distribution, including discrete distributions such as binomial, Poisson, or uniform, using chi-square statistics and null/alternative hypotheses.

Apply a goodness of fit test in Excel by comparing observed versus expected daily customers, computing chi-square and degrees of freedom, and concluding if the distribution differs from uniform.

use contingency tables to test independence between two categorical variables with the chi square distribution, computing observed and expected frequencies from row and column totals, and degrees of freedom (r-1)(c-1).

Learn to use a 3x3 contingency table in Excel to test independence between age group and product, compute observed and expected counts, and apply chi-square at 0.05.

Explore correlation and regression with a focus on simple linear regression, using Excel for typical cases to predict values from one input to one output.

Explore how the correlation coefficient measures the strength and direction of relationships between variables, with Pearson's r and scatter plots, and note that correlation does not imply causation.

Discover the coefficient of determination r squared, the proportion of variance in dependent variable explained by the independent variable, and its link to the correlation coefficient in simple linear regression.

Plot and analyze relationships in Excel by creating a scatter plot, computing Pearson's correlation coefficient, and interpreting results for single and multi-variable data sets.

Explore Excel’s built-in correlation tools, including CORREL, PEARSON, and RSQ, to measure how hours studied relate to test scores, and see RSQ as the square of the correlation.

Explore simple linear regression to model the relationship between two variables with a linear equation, and learn to compute b1 and b0 from sample data using scatter plots.

Explore how to perform linear regression in Excel using built-in functions like slope, intercept, forecast.linear, trend, and display the regression equation and r-squared on scatter plots.

Use Excel's data analysis toolpak to perform regression, interpret r square, slope and intercept, and verify assumptions with residual and normal plots.

Explore real-life applications of descriptive and inferential statistics, including correlation and regression, using large datasets, with two practical projects and solution videos.

Apply hypothesis testing to assess whether gender and product category are related using a 1000-record Excel dataset (beauty, clothing, electronics) at 95% confidence.

Explore how to build a contingency table in Excel to assess the relationship between gender and product category purchases, compute observed and expected counts, chi-square statistic, and interpret results.

Investigate whether male and female nurses have different hourly rates using a two-sample z test on filtered nurse data, testing mu_male equals mu_female at 95% confidence.

Explore performing a two-sample z test in Excel to compare male and female nurses' hourly rates, including data preparation, filtering, and interpreting a p-value to conclude no significant difference.