Pareto 80/20 Curves, Variance Bridge Helpers & Scatter XY Diagnostics

Who this is for

Stage 7: Chart-Ready Staging Tables & Visual Analytics — Designed for learners mastering descending pareto staging, cumulative % & 80/20 vital-few rollups, waterfall floating base/up/down columns, pocket price & headcount bridges, diverging variance bars, tornado sensitivity charts & countifs histograms, scatter xy numeric pairing, quadrant gates, slope/correl & z-score outliers through interactive spreadsheet examples.

What you will practise

• Spill the descending defect table in D2 using =SORT(A2:B6, 2, -1), then insert a Column chart on D1:E6.
• Compute Cumulative Count in C2:C6 (=SUM($B$2:B2)), Cumulative % in D2:D6 (=ROUND(C2/$B$7, 4)), and Pareto Bucket in E2:E6 (=IF(D2 <= 0.8, "VITAL", "TAIL")).
• Sum Tickets in F2:F3 with SUMIF, count Causes in G2:G3 with COUNTIF, and insert a Column chart strictly on E1:F3.
• Compute Invisible Base in B3:B6 (=SUM($F$2:F2) + MIN(0, F3)), Up Step in C3:C6 (=MAX(0, F3)), and Down Step in D3:D6 (=MAX(0, -F3)), then insert a Column chart on A1:D7.
• Compute Running Price Level in C2:C5 (=SUM($B$2:B2)), Net Pocket Price in C6 (=SUM(B2:B5)), and % of List Price in D2:D6 (=ROUND(C2 / $B$2, 4)).
• Link Opening FTE in B3:B5 to prior Closing (E2:E4), compute Closing FTE in E2:E5 (=B2 + C2 - D2) and FY Check Diff in E6 (=B2 + SUM(C2:C5) - SUM(D2:D5) - E5), then insert a Column chart on A1:E5.
• Compute Beat (+$k) in F2:F6 (=MAX(0, C2 - B2)) and Miss (-$k) in G2:G6 (=MIN(0, C2 - B2)), then insert a horizontal Bar chart on E1:G6.
• Calculate Cost per Acquisition ($) in D2:D7 (=ROUND(B2 * 1000 / C2, 2)), then select strictly the paired numeric XY columns B1:C7 and insert a Scatter chart.

Your learning path

1. 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.

2. Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)

   Detect cold-chain, blood-glucose, or machining readings that fall strictly outside an acceptable [Min, Max] window using OR(B2 < MIN(X,Y), B2 > MAX(X,Y)) and count total two-sided excursions via COUNTIF.

3. 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.

4. COUNTIF & SUMIF Across Non-Contiguous Ranges (Additive & VSTACK)

   Count matching rows and sum quantities from two separate blocks, with literal criteria and a dynamic-array alternative.

5. Course Completion & Compliance Pass-Rate Summary With COUNTIFS

   Calculate LMS training compliance rates (%) requiring both Status = "Completed" and Exam Score >= Passing Cutoff using COUNTIFS(C2:C7, "Completed", D2:D7, ">=" & F2) / COUNTA(A2:A7).

6. Two-way summary matrix with SUMIFS and COUNTIFS (live cross-tab table)

   Build a live PivotTable-style Region x Category grid that updates automatically without manual refresh using mixed references ($E2 and F$1).

7. XY Scatter Plot Trendline Formulas: Live Cell SLOPE, INTERCEPT, FORECAST & R²

   Extract dynamic XY scatter-plot trendline equations directly into worksheet cells—instead of copying static chart label text—using SLOPE(Y, X), INTERCEPT(Y, X), FORECAST(x_new, Y, X), and CORREL(X, Y)².

8. Count & Sum Odd vs Even Numbers in a Range (MOD, ISODD & SUMIF)

   Test integer parity with MOD(n, 2) or ISODD/ISEVEN and count or sum odd-numbered vs even-numbered street addresses, lot IDs, and quantities cleanly across any spreadsheet engine.