Advanced Excel Formulas & Functions Masterclass

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.

• Price a sleeping bag from a region matrix, find stock by warehouse AND SKU, and apply volume-discount tiers for Cairnfield Outdoor Supply.
• Total revenue in a date window, count tent orders with a wildcard, find the largest order while excluding one rep, and build a region × channel grid with one copyable formula.
• Calculate net revenue without a helper column, filter it with AND and OR conditions inside SUMPRODUCT, and work out a properly weighted average discount.
• Analyse Q3 budget vs actual with single-cell array formulas: total absolute variance, average deviation, lines over tolerance and the line with the biggest overrun.
• Translate tier codes, grade customers on two conditions, map dates to an April-to-March fiscal quarter and look up reps with a clean fallback.
• Pull the weight out of a messy export, count tags, build price labels with proper formatting and list every SKU under a price limit in one cell.
• Set invoice due dates, count business days with closing days, assign Monday-based week numbers and flag orders that made the same-day cutoff.
• Rank the sales team, show each rep's percentile, average the top three and find the median revenue of one region.