The exact SLA boundary is marked late
The task allows equality. A >= comparison changes the stated rule.
Classify deliveries against an SLA
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill D2:D8. For Delivered rows, show Late only when numeric nonnegative hours exceed G2; otherwise show On target. Show Review data for an invalid completed measurement and Not completed for every other state.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: D2, D3, D4, D5, D6, D7, D8. 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
Separate unfinished deliveries, missing measurements and genuine zero-hour observations before deciding whether a completed delivery is late.
THE QUICK ANSWER01
Check completion first, validate its recorded duration next, then compare a valid duration with the fixed SLA cell. Equality meets the SLA; an empty measurement must not masquerade as a zero.
ƒx
=IF(B2<>"Delivered","Not completed",IF(ISNUMBER(C2),IF(C2<0,"Review data",IF(C2>$G$2,"Late","On target")),"Review data"))For the example data
D2 On targetD3 LateD4 On targetD5 Not completedD6 Review dataD7 Review dataD8 Review data
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A label represents a specific rule
The original 24-hour limit includes an exact 24-hour delivery. The 24.5-hour delivery is Late. An unfinished delivery stays Not completed even when its current age exceeds the limit; it has not entered the completed-delivery population.
Missing and zero are different
A completed duration of zero is numeric and can meet the SLA. A blank or a text number has no accepted measurement under this rule, so it shows Review data. ISNUMBER supplies that distinction before a comparison can coerce a blank.
Keep the judgment explicit
This is a fictional training rule, not a claim about any carrier contract. In a real report, agree whether the limit is inclusive and what the measured interval represents. This worksheet uses elapsed hours already recorded as numbers.
Read the full step-by-step walkthrough
- Decide whether the delivery is complete
Only Delivered rows receive an SLA classification. A large in-progress duration must still show Not completed.
- Check that a duration was measured
ISNUMBER distinguishes a numeric zero from a blank or text value. A completed row without a valid numeric measurement needs review.
- Reject a negative duration
A negative number is numeric but cannot represent elapsed hours in this task. Test it before applying the limit.
- Compare with the fixed limit
A duration greater than G2 is Late. The exact boundary and numeric zero are On target. Dollar signs keep the SLA reference fixed when filled down.
- Fill the whole classification column
Keep the row references relative and the SLA reference fixed. Test all seven rows and the different datasets.
Things to watch
A missing duration passes as zero
Validate the measurement before comparing it with the SLA.
The SLA shifts as the formula fills
The same $G$2 limit applies to every row. Fix both its row and column.
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.