Every service gets equal weight
AVERAGE(B2:B7) ignores the case counts. Its result can differ even when all estimates are correct.
Weight a workload by its case counts
About this lesson
Combine per-case estimates using the number of cases instead of giving every service the same influence.
Task requirements
In G2, calculate case-weighted minutes from B2:B7 and C2:C7. Show Review weights for a negative case count or zero total volume.
Teaching · read-only worksheet ·
Step 1 / 5Read the units
More
Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: G2. Browser calculations may differ from your Excel version.
Practice stays on this device. No worksheet data is uploaded.
Choose the result you need
SUMPRODUCT multiplies minutes per case by the count on the same row. The products total 320 minutes across 15 cases: 320/15, about 21.33 minutes per case. AVERAGE(B2:B7) instead gives each service equal influence and returns about 33.33.
=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)Use this short formula when estimates and counts are numeric, counts are nonnegative and total count is positive. The two ranges must have matching dimensions. Counts need not add to 1; SUM divides by their actual total. The guarded primary task also checks negative and zero-total counts.
All result formulas
- G2
=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)
| Row | A | B | C | F | G |
|---|---|---|---|---|---|
| 1 | "Service" | "Minutes / case" | "Case count" | "Metric" | "Value" |
| 2 | "Basic" | 10 | 9 | "Weighted mean" | 21.333333333333332 |
| 3 | "Extended" | 40 | 1 | — | — |
| 4 | "Special" | 80 | 0 | — | — |
| 5 | "Night" | 20 | 2 | — | — |
| 6 | "Backup" | 0 | 0 | — | — |
| 7 | "Overflow" | 50 | 3 | — | — |
These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practiceMicrosoft reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
All worked examples
Expand an example to read its complete source data and checked results, then download the matching workbook. Cell references belong to that example. Opening it leaves the primary practice and your progress unchanged.
Use case-count weights
SUMPRODUCT multiplies minutes per case by the count on the same row. The products total 320 minutes across 15 cases: 320/15, about 21.33 minutes per case. AVERAGE(B2:B7) instead gives each service equal influence and returns about 33.33.
=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | F | G |
|---|---|---|---|---|---|
| 1 | "Service" | "Minutes / case" | "Case count" | "Metric" | "Value" |
| 2 | "Basic" | 10 | 9 | "Weighted mean" | 21.333333333333332 |
| 3 | "Extended" | 40 | 1 | — | — |
| 4 | "Special" | 80 | 0 | — | — |
| 5 | "Night" | 20 | 2 | — | — |
| 6 | "Backup" | 0 | 0 | — | — |
| 7 | "Overflow" | 50 | 3 | — | — |
- G2
- 21.333333333333332
Use this short formula when estimates and counts are numeric, counts are nonnegative and total count is positive. The two ranges must have matching dimensions. Counts need not add to 1; SUM divides by their actual total. The guarded primary task also checks negative and zero-total counts.
Download this example's workbookKeep a zero estimate
Basic has a numeric estimate of 0 and two cases. Those cases contribute 0 minutes but still belong in the denominator. The total is 55 minutes across 6 cases, so G2 is 55/6, about 9.17. A service with zero cases contributes to neither total.
=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | F | G |
|---|---|---|---|---|---|
| 1 | "Service" | "Minutes / case" | "Case count" | "Metric" | "Value" |
| 2 | "Basic" | 0 | 2 | "Weighted mean" | 9.166666666666666 |
| 3 | "Extended" | 30 | 1 | — | — |
| 4 | "Special" | 90 | 0 | — | — |
| 5 | "Night" | 15 | 1 | — | — |
| 6 | "Backup" | 40 | 0 | — | — |
| 7 | "Overflow" | 5 | 2 | — | — |
- G2
- 9.166666666666666
Numeric zero is a value, not missing data. Do not remove its positive case count or divide only by rows with positive estimates. This short formula still requires a positive total count. Text or blank estimates/counts are outside this task; negative durations are unsupported.
Download this example's workbookReview zero total weight
Every count is zero, so there is no case-weighted mean. The IF guard returns Review weights before dividing by the total. This is missing workload, not an observed duration of zero.
=IF(OR(MIN(C2:C7)<0,SUM(C2:C7)=0),"Review weights",SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7))Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | F | G |
|---|---|---|---|---|---|
| 1 | "Service" | "Minutes / case" | "Case count" | "Metric" | "Value" |
| 2 | "Basic" | 10 | 0 | "Weighted mean" | "Review weights" |
| 3 | "Extended" | 30 | 0 | — | — |
| 4 | "Special" | 80 | 0 | — | — |
| 5 | "Night" | 20 | 0 | — | — |
| 6 | "Backup" | 0 | 0 | — | — |
| 7 | "Overflow" | 50 | 0 | — | — |
- G2
- "Review weights"
The unguarded SUMPRODUCT/SUM recipe divides by zero here. Keep the review label distinct from a valid numeric zero. This task requires numeric source values and nonnegative duration estimates; it does not silently treat blank or text inputs as zero.
Download this example's workbookReview a negative count
The counts add to 13, but Extended has -1 case. A positive sum alone cannot validate this workload. MIN detects the negative count and returns Review weights.
=IF(OR(MIN(C2:C7)<0,SUM(C2:C7)=0),"Review weights",SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7))Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | F | G |
|---|---|---|---|---|---|
| 1 | "Service" | "Minutes / case" | "Case count" | "Metric" | "Value" |
| 2 | "Basic" | 10 | 9 | "Weighted mean" | "Review weights" |
| 3 | "Extended" | 40 | -1 | — | — |
| 4 | "Special" | 80 | 0 | — | — |
| 5 | "Night" | 20 | 2 | — | — |
| 6 | "Backup" | 0 | 0 | — | — |
| 7 | "Overflow" | 50 | 3 | — | — |
- G2
- "Review weights"
Case counts are nonnegative under this task definition. Signed weights in other mathematical models require a different interpretation; this review rule is not a universal ban on negative inputs to SUMPRODUCT. Keep both checks in the primary formula.
Download this example's workbookHow it works
The weights choose the question
In the original workload, the total is 320 estimated minutes across 15 cases, so the weighted mean is 320/15. An ordinary average gives each service one vote even when a service has many cases or no current cases. Use that different statistic only when equal service influence is the intended question.
A zero weight is useful data
A service can have an established per-case estimate and no work in the current batch. Its zero count removes it from both the weighted numerator and denominator. Increasing only its estimate must not change the result.
Review missing or negative volume
A zero total count has no case-weighted average. Negative counts also need review under this task definition, even if their sum stays positive. The worksheet expects numeric estimates and numeric counts; a text or blank count is reported as unsupported. Duration estimates must be nonnegative.
Read the full step-by-step walkthrough
- Read the units
B contains minutes per case and C contains case counts. Their product is an estimated number of minutes for that service workload.
- Build the weighted total
SUMPRODUCT multiplies corresponding values and adds the products. A zero weight contributes zero, even when its per-case estimate is high.
- Keep the denominator aligned
Divide by total cases, not by the number of services. Both numerator and denominator describe the same workload.
- Review unusable weights
Negative case counts are outside this workload definition. No cases also leaves no weighted mean; the formula returns a review label for either condition.
- Test a high-value zero-weight service
Change its estimate while leaving its case count at zero. The weighted result should stay the same. Then make its count positive and see it enter both totals.
Things to watch
The zero-count service changes the mean
A zero weight must contribute to neither the numerator nor the denominator.
A positive total hides a negative count
Checking only the total count misses negative rows. Inspect the minimum count as well.
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.