Excel Statistical Functions: AVERAGEIFS, MEDIAN, PERCENTILE, RANK & FREQUENCY

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Describe a typical Orchard Row sales rep with the mean, the median, the mode and a trimmed mean, and see how one star performer pulls the average.
• Build a region summary for Orchard Row with AVERAGEIFS, MAXIFS and MINIFS, and count reps that meet three conditions with COUNTIFS.
• Measure how much Orchard Row's days-to-close vary with the range, the sample standard deviation and variance, and the interquartile range.
• Set a top-10% bonus threshold with PERCENTILE.INC, give every rep a percent rank and sort them into four quartile bands.
• Rank Orchard Row's reps by deals with RANK.EQ and RANK.AVG, see how ties are treated, and list the top-3 and bottom-3 revenues with LARGE and SMALL.
• Group 40 web orders into $50 bins with COUNTIFS, add the share of each bin and draw a histogram.
• Measure how strongly deals and revenue move together, turn every revenue into a z-score and draw a scatter chart with a trendline.
• Flag outliers in Orchard Row's revenue with the 1.5 × IQR rule and complete a summary table that compares all reps with the team without outliers.