Time-Series Indexing, Moving Averages, Target Bands & Line/Area Charts

Who this is for

Stage 7: Chart-Ready Staging Tables & Visual Analytics — Designed for learners mastering monthly trend staging, base-100 indexing & 3w/7d moving averages, dynamic target corridors, ytd quota burnups & missing-month bridges, backlog & underwater drawdown area charts, 12-month yoy overlays, seasonality indices & forecast handoffs through interactive spreadsheet examples.

What you will practise

• Calculate monthly revenue ($k) in E2:E6 using SUMIF against $A$2:$A$9 and $B$2:$B$9, then insert a Line chart on D1:E6.
• Rebase Enterprise ($k) into E2:E6 and Self-Serve ($k) into F2:F6 at Base = 100 (rounded to 2 decimals), then insert a Line chart on D1:F6.
• Compute the trailing 3-week moving average rounded to 1 decimal in C4:C7 using AVERAGE, then insert a dual-series Line chart on A1:C7.
• Populate Target in C2:C6 (=$H$2), Lower Band in D2:D6 (=ROUND($H$2*(1-$H$3), 1)), and Upper Band in E2:E6 (=ROUND($H$2*(1+$H$3), 1)), then insert a Line chart on A1:E6.
• Compute Cumulative YTD ($k) in D2:D7 via expanding SUM($C$2:C2), compute Linear Pace ($k) in E2:E7 via ROUND($H$2 / 6 * A2, 0), and insert a Line chart on B1:E7.
• In C3:C5, keep B3:B5 when populated or interpolate ROUND(AVERAGE(prior, next), 1) when ISBLANK is TRUE, then plot the bridged trend on D1:E6.
• Calculate Closing Backlog ($k) in B2:B6 from locked Opening Backlog ($G$2) plus expanding SUM($C$2:C2) minus SUM($D$2:D2), then insert an Area chart on A1:B6.
• Pull Current Year ($k) into C2:C13 from the unsorted export ($F$2:$G$13) using XLOOKUP on Month (A2:A13), then insert a Line chart on A1:C13.

Your learning path

1. Dynamic two-way sum and average: aggregate a chosen column by row criteria

   Select a monthly metric column with XLOOKUP, filter its rows, and calculate a dynamic sum or average.

2. Sum & Average Daily Revenue by Day of Week (WEEKDAY + SUMIF)

   Aggregate daily POS or e-commerce transactions by ISO day of week (Monday = 1 through Sunday = 7) and split weekday vs. weekend totals using WEEKDAY(A2, 2) + SUMIF.

3. Sum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)

   Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).

4. Cell Equals, Count, or Sum One of Many Values (Allowlist COUNTIF + SUMPRODUCT)

   Replace brittle 10-way OR chains with a range-driven COUNTIF allowlist flag to test, count, and sum rows matching any value in an editable lookup list.

5. Non-Volatile Dynamic N-Period Sum & Average Without OFFSET (SUMIFS & AVERAGEIFS)

   Replace slow volatile SUM(OFFSET(B2,0,0,N,1)) rolling windows with dependency-tracked SUMIFS(B2:B7, A2:A7, "<=" & E2) and AVERAGEIFS for instant multi-sheet recalculation.

6. Sum, Max & Average a Variable Column by Header (CHOOSECOLS + MATCH)

   Select a numeric column by a quarter header and calculate its sum, maximum, minimum and average with missing-header checks.

7. XLOOKUP across two identifier columns: OR matches and precedence

   Normalize dual-column matches and choose between first matching row and primary-column priority.

8. Lookup the date or name of a MAX or MIN value: first and last matching rows

   Return the label associated with a maximum or minimum, and choose the first or last matching row when values tie.