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
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
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.
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: G2. 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
Combine per-case estimates using the number of cases instead of giving every service the same influence.
THE QUICK ANSWER01
Multiply each per-case estimate by its case count, add those products, then divide by total cases. A zero-volume service contributes nothing. Review the weights before division when any count is negative or the whole workload is empty.
ƒx
=IF(OR(MIN(C2:C7)<0,SUM(C2:C7)=0),"Review weights",SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7))For the example data
G2 21.333333333333332
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How 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.