The report uses the wrong average
The required decision is based on G6, the case-weighted mean. G5 is a separate equal-service comparison.
Build an operational review with the right denominator
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill G5:G7 for the region/state in G2:G3. G5 is the unweighted mean of services with positive case counts; G6 is the case-weighted mean, with explicit empty/negative-weight states; G7 compares numeric G6 with target G4 or carries its text state.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5, G6, G7. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Compare two legitimate averages for one selected population and base the review flag on the agreed case-weighted metric.
THE QUICK ANSWER01
Build three outputs separately. First compare active services equally, then weight their per-case estimates by case counts. The review decision uses the weighted result and the selected target. Keep missing-volume and invalid-weight states visible.
G5
=IFERROR(AVERAGEIFS(C2:C7,A2:A7,$G$2,B2:B7,$G$3,D2:D7,">0"),"No active cases")G6
=IF(SUMPRODUCT((A2:A7=$G$2)*(B2:B7=$G$3)*(D2:D7<0))>0,"Review weights",IF(SUMIFS(D2:D7,A2:A7,$G$2,B2:B7,$G$3)=0,"No active cases",SUMPRODUCT((A2:A7=$G$2)*(B2:B7=$G$3)*C2:C7*D2:D7)/SUMIFS(D2:D7,A2:A7,$G$2,B2:B7,$G$3)))G7
=IF(ISNUMBER(G6),IF(G6>$G$4,"Review","On target"),G6)For the example data
G5 16.666666666666668G6 10.833333333333334G7 On target
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Two averages answer different questions
The three active East/Delivered services have estimates 10, 40 and 0, so their equal-service mean is 50/3. They carry 9, 1 and 2 cases, giving 130 estimated minutes across 12 cases. Their case-weighted mean is 130/12. The zero-volume service remains in the source but enters neither metric.
The review rule must name its metric
At a target of 15, the service mean exceeds the target while the case-weighted mean does not. G7 uses the case-weighted result, so the original report is On target. That aggregate result does not say that every service meets the target.
Keep all filters and exceptional states aligned
Apply region and state to both the weighted numerator and denominator. Review a selected negative count even when total volume stays positive. A negative count in another region is outside this report. If the selected group has no active volume, preserve No active cases instead of inventing a zero-minute mean.
Enter the three formulas independently
Use the answer-cell selector in practice to enter G5, G6 and G7. Their formulas perform different jobs; filling the G5 formula down would only move its ranges. The solution panel lists each complete formula. The blank challenge checks every authored dataset before saving completion.
Read the full step-by-step walkthrough
- Fix the report population
Both averages use the selected region and state. Other source rows remain visible but must not enter either report numerator.
- Calculate the active-service mean
G5 gives each selected positive-volume service equal influence. Exclude zero-volume services from this comparison.
- Calculate the case-weighted mean
Enter the separate G6 formula. Its numerator and denominator use the same region/state mask. A zero-volume service contributes nothing.
- Review invalid or absent volume
A negative selected count produces Review weights. No selected volume produces No active cases. Negative counts outside the selected population must not change this report.
- Base the decision on the agreed metric
Enter the G7 formula separately. Compare numeric G6 with G4; preserve its text state when the weighted result cannot be established.
- Reconcile the two averages
The original active-service mean is 50/3; the case-weighted mean is 130/12. A target of 15 gives different judgments depending on which statistic is chosen. This task explicitly uses the weighted one.
Things to watch
The weighted denominator includes another region
A filtered numerator divided by all case counts describes no consistent population.
Filling G5 down replaces the other calculations
Enter G6 and G7 separately using their complete formulas.
About this example
Practice uses the local browser spreadsheet engine. Only the supplied cases are checked here; native Excel and Google Sheets recalculation remain unverified. The starter download retains original data, while the practice export retains your current worksheet.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.