Weighted Analytics, Clean LET Formulas & Dynamic Arrays

Who this is for

Stage 2: Logical Decisions, Conditional Math & Dynamic Formulas — Designed for learners mastering sumproduct masks & blended margins, large, small, tie-broken rank & percentile, clean excel-valid let variables, spilled unique, sortby, filter, sequence & vstack/take through interactive spreadsheet examples.

What you will practise

• Calculate total gross billing in F3 with SUMPRODUCT(C2:C6, D2:D6) and filtered EMEA Rush billing in F4 using boolean-masked SUMPRODUCT.
• Compute total gross profit in E2 with SUMPRODUCT and zero-guarded blended gross margin ratio in E3.
• Populate Top-3 revenues in E2:E4 with LARGE($B$2:$B$8, D2) and Bottom-3 revenues in F2:F4 with SMALL($B$2:$B$8, D2).
• Calculate competition rank in C2:C6 with RANK and tie-broken unique rank in D2:D6 with RANK + expanding COUNTIF - 1.
• Compute MEDIAN in E2 and AVERAGE in E3, then evaluate P75 and P90 in E4:E5 using PERCENTILE($B$2:$B$10, D4:D5).
• In D2:D5, use LET + IF to apply a 15% discount when gross >= 2500, 10% when gross >= 1000, and 0% below 1000.
• In E2:E5, chain netRev = B2*(1-C2) and shipFee = IF(netRev<1500, 75, 0) inside LET to compute net contribution netRev - D2 - shipFee.
• Spill distinct SKUs from A2:A8 starting at D2 using UNIQUE, and count distinct customers in B2:B8 inside F2.

Your learning path

1. Sum the Bottom N Values Matching Criteria (FILTER, SORT & TAKE Pipeline)

   Total the N lowest vendor quotes, fastest lap times, or smallest Regional invoices within a specific category using SUM(TAKE(SORT(FILTER(...)), N)) with graceful IFERROR handling.

2. Weighted average with multiple criteria (SUMPRODUCT + SUMIFS)

   Calculate volume-weighted unit price or yield strictly for rows matching Region, Channel, or Status filters.

3. Rank Without Ties formula: Unique Sequential Ranks (RANK.EQ + COUNTIF)

   Assign unique sequential positions to five numeric scores, resolving equal scores by their source-row order.

4. Combine Duplicate Rows & Sum Values Non-Destructively (UNIQUE, COUNTIF & SUMIFS)

   Consolidate repeated SKU or customer line items into one summary row per key while preserving raw transaction audit trails and reconciling grand totals.

5. LARGE, SMALL, and RANK.EQ: extract Nth values and handle ties

   Pull the 1st, 2nd, or 3rd highest and lowest numbers without sorting the table, see how tied scores repeat in LARGE and skip ranks in RANK.EQ, and catch #NUM! bounds.

6. Average or sum the last N numeric entries with FILTER and TAKE

   Select the last N numeric cells in source-row order, retaining zero and negative values while skipping text, blanks and error cells.

7. Sum top N (or bottom N) values with criteria: FILTER + SORT + TAKE

   Total the top K largest deals inside a specific region—even when that region has fewer than K rows—by chaining FILTER, SORT, and TAKE.

8. Mark, Count & Sum Rows Matching Mixed AND + OR Rules (IF + COUNTIF + SUMIF)

   Flag audit rows meeting compound AND + OR criteria (e.g., Amount >= $1,000 AND Status is Urgent OR Escalated), then count and sum flagged exposure cleanly.