Statistics and Data Analysis: practice exercises
Practice the statistics that most workplace questions need: mean and median, variance and standard deviation, ranks and percentiles, correlation, and simple linear forecasting with FORECAST and TREND. The tasks use small, readable data sets, so you can sanity-check each answer by eye. Good for students, researchers and analysts who want to apply Excel statistical functions correctly and understand what the numbers actually say.
57 exercises · level: Analyst
Start with the first task Study the course: Statistics and Data Analysis →
Showing 57 of 57
Statistics
In C2:C9, enter each rep's rank by revenue.
In F1, find the median revenue.
In F3:F5, show the three best results. The place numbers are already in D3:D5.
In F6, find the weakest result with the SMALL function.
In B8, use one formula to calculate the cost of the whole cart.
In H2, calculate the grade average weighted by credits (a weighted average).
In F1, calculate the standard deviation of sales.
In F2, calculate the correlation between ad spend and sales.
In F5, forecast sales for the ad spend in F4.
In G1, find the median of the average check (column B).
In D2:D9, rank each branch by number of checks (1 means the most).
In G2 and G3, show the highest and second-highest average check.
In G4, use one formula to calculate the whole chain's revenue: the sum over branches of "average check x checks".
In G5, calculate the average check for the whole chain (a weighted average).
Sort A2:C9 by number of checks, largest first. Keep the rows intact.
In F1, calculate the standard deviation of sales.
In F2, calculate the correlation between temperature and sales.
In F4, forecast sales for the temperature in F3.
In I9, calculate the NPV of buying the freezer at the rate in I1.
In I10, calculate the IRR: the return on the freezer investment.
Use Goal Seek to set the serving price in F6 so that the profit in F10 is exactly $2,000.
Distribution and Outliers
In F2 and F3, find the first and third quartiles of delivery time.
In F4, calculate the IQR. In F5 and F6, calculate the outlier fences by Tukey's rule (multiplier in F1).
In D2:D17, write "outlier" if the time is outside the fences in F5:F6, otherwise "normal".
In F7, count how many orders turned out to be outliers.
In F9, find the delivery-time percentile for the level in F8: 90% of orders arrive faster than this time.
In C2:C17, calculate each order's z-score with the STANDARDIZE function (the mean and standard deviation are already in F10 and F11).
Forecasting
In I2:I5, calculate the seasonal index of each quarter: the average sales of the quarter / the average of all sales.
In D2:D17, look up the seasonal index for the quarter in column B, for both the history and the four future periods.
In E2:E13, remove seasonality from revenue: revenue / seasonal index.
In I7, find the slope of the deseasonalized trend: how many $M revenue grows per quarter.
In F14:F17, build the forecast: the trend of the deseasonalized series for the period in A x the seasonal index in D.
In I9, add up the year 4 forecast. In I10, calculate the growth over the actual year 3 revenue (C10:C13).
Hypothesis Testing
In E2:F4, calculate each group's number of participants, average score, and variance (VAR.S).
In E7, calculate the t-statistic (new - old) using the pooled variance. In E8, calculate the degrees of freedom n₁+n₂-2.
In E9, find the two-tailed p-value for the t in E7, rounded to 4 decimal places.
In E10, find the critical t for the level α in E6, rounded to 4 decimal places. Compare it with |t| in E7.
In E11 and E12, get the p-value straight from the data with T.TEST: type 2 and type 3, two-tailed, rounded to 4 decimal places.
In E13, state the conclusion of Welch's test: "significant" if p in E12 is below α, otherwise "not significant".
In F2:H4, calculate each display's number of days, average, and variance (VAR.S).
In F6, find the grand mean of all observations. In F7, find SSB. In F8, find SSW.
In F9 and F10, calculate the degrees of freedom (k-1 and N-k). In F11, calculate the F-statistic.
In F13, find the p-value for the F in F11, rounded to 4 decimal places.
In F14, find the critical F for the α in F12, rounded to 4 decimal places.
In F15, state the conclusion: "means differ" if p is below α, otherwise "not significant".
A/B Tests
In B4:C4, calculate each group's conversion rate and give those cells the Percentage format.
In C5, calculate the uplift: the relative gain in conversion of B over A.
In B7, find the pooled conversion rate of both groups. In B8, find the standard error of the difference.
In B9, calculate the z-statistic: the difference in conversion rates B - A divided by the standard error.
In B10, find the two-tailed p-value, rounded to 4 decimal places.
In B12, make the decision: "ship B" if p is below α and B's conversion is higher, otherwise "keep testing".
In B8:C8, calculate each group's conversion rate. In B9:C9, calculate revenue per visitor (conversion rate x average order value).
In B10, find the critical z for a two-tailed interval at the α in B6, rounded to 4 decimal places.
In B11, calculate the conversion difference B - A. In C11, its standard error. In B12:C12, the confidence interval bounds.
In B14:C14, calculate each group's margin of error for the average order value with CONFIDENCE.NORM (n is the number of orders), rounded to 2 decimal places.
In B15, calculate the difference in average order values B - A. In C15, its standard error. In B16:C16, the confidence interval of the difference.
In B18, make the decision by the rule in the theory: "ship B", "keep A", or "need more data".
No exercises match these filters.