The zero disappeared
A recorded numeric zero belongs in the average. Do not add a >0 condition unless that is the intended population.
Average the records that qualify
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
In G5, average numeric hours for the region in G2 and state in G3. Include numeric zero, ignore blank/text measurements, and show No numeric records when none qualify.
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: G5. 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
Choose the intended region and delivery state, keep real zeros, and distinguish missing measurements from observations.
THE QUICK ANSWER01
AVERAGEIFS selects numeric hours whose region and state both match. IFERROR supplies an explicit label when the selection has no numeric observations. Zero is an observation; an empty measurement is not.
ƒx
=IFERROR(AVERAGEIFS(C2:C8,A2:A8,$G$2,B2:B8,$G$3),"No numeric records")For the example data
G5 12
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
The denominator is a count of observations
The original East/Delivered hours are 0, 12, 24, a blank and a text note. The three numeric observations total 36, so their average is 12. Dividing by all five matching rows would invent two observations. Excluding the zero would change the question again.
Conditions are literal labels in this task
Region and state matching ignores letter case. Choose nonempty labels such as East or Delivered. Wildcards, comparison expressions, numeric criteria and boolean labels belong to a different exercise; they are reported as unsupported rather than graded as a formula error.
Use an error fallback deliberately
IFERROR can hide more than an empty average. Here the input contract restricts the worksheet to supported measurements and equal-sized ranges. If a different workbook produces an unexpected fallback, inspect its source and arguments before treating the label as evidence that there are no records.
Read the full step-by-step walkthrough
- Define the population
The region and state cells describe the population. A Pending row must not enter a Delivered-only result.
- Choose the measurement
C2:C8 contains observed hours. Numeric zero counts. A blank or a text note has no numeric duration to contribute.
- Apply both conditions
AVERAGEIFS uses only rows meeting both literal-label conditions. The ranges cover the same seven source rows.
- Keep the absent-data state visible
No qualifying numeric records cannot establish a zero-hour average. The fallback label preserves that distinction.
- Change the selection
Try another region or state, then check your formula on every authored dataset. Changing the criteria should not require rewriting the ranges.
Things to watch
The average includes unfinished deliveries
A region match alone does not satisfy the task. Apply the state condition as well.
Missing hours become zero
A blank/text measurement is excluded from the average, not replaced with a new observation.
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.