AI-Assisted Workbook Audits, Attachment Staging & Team Capstones

Who this is for

Stage 8: Real-Time Collaboration, Review Gates & AI Workflows — Designed for learners mastering ai selection context hygiene, edge-case formula verification & attachment staging, voice/text multi-step plan audits & suggester vs contributor ai boundaries, isformula hardcode detection, cross-tab harnesses & inherited lock repairs, scenario stress-testing, final release checklists & end-to-end team capstones through interactive spreadsheet examples.

What you will practise

• Standardize Raw Region (A2:A5) into D2:D5 using =UPPER(TRIM(A2)), then compute contiguous ARR ($k) in E2:E5 as Deals * Avg Deal ($k).
• Compute Zero-Safe Attainment % in D2:D6 via =IF(B2 = 0, 0, ROUND(C2 / B2 * 100, 1)), then classify E2:E6 as "ELIGIBLE" when B2 > 0 AND D2 >= 100 (otherwise "RAMP_OR_BELOW").
• Convert Imported Raw Amount (B2:B5) to numbers in C2:C5 via =VALUE(TRIM(B2)), then compute Staged Sum in F3 and Reconciliation Gate ("RECONCILED" vs "MISMATCH") in F4.
• On PlanAudit, aggregate Bookings ($k) in B2:B4 from RawBookings!$A$2:$B$7 via SUMIF, then insert a Column chart strictly on A1:B4.
• Compute Effective Live Value in F2:F5 via =IF(OR(D2 <> "Suggester", E2 = "APPROVED"), C2, B2), then count Pending Suggester Proposals in H2 using COUNTIFS.
• Audit D2:D6 in E2:E6 with =IF(ISFORMULA(D2), "OK_FORMULA", "HARDCODED_OVERRIDE"), then count total overrides in E7 using COUNTIF.
• Compute Cross-Tab Gap in C2:C4 as Reported B2 minus SUMIF on DetailLedger!$A$2:$B$7, then set C6 to "SAFE_TO_INVITE" when C2:C4 all equal 0 (otherwise "BLOCK_SHARE").
• Calculate Abs Variance % (1 decimal) in E2:E6 via =ROUND(ABS(C2 - B2) / B2 * 100, 1), then output D2 & ": REVIEW" in F2:F6 when E2 >= $G$2 (otherwise "WITHIN_BAND").

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

3. Position & Exact Cell Address of Max or Min Value (XMATCH & ADDRESS)

   Find both the relative list position (1..N) and exact worksheet cell address (such as "B4") of the maximum or minimum value in a column using XMATCH(MAX(range), range, 0) and ADDRESS.

4. Count & Sum Cells Ending With or Starting With Specific Text (COUNTIF / SUMIF)

   Match SKU suffixes ("*" & suffix) and regional prefixes (prefix & "*") dynamically with COUNTIF and SUMIF while preventing blank-affix wildcard traps.

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

6. 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).

7. Check If a Value Exists in a Range (COUNTIF & XMATCH Membership Test)

   Check a nonblank ID against an approved list, distinguish literal matching from COUNTIF criteria, and test whether either of two watch-list IDs is present.

8. SUMIF & COUNTIF when cells contain a literal asterisk (*) or question mark (?)

   Escape wildcard characters with a tilde ("*~**") so SUMIF and COUNTIF match literal asterisks, question marks, or tildes instead of every text row.