What you will fix
You will reproduce #NUM! in D2 because C2-B2 equals -10, diagnose the invalid negative input passed to SQRT, then correct the subtraction order in E2 and verify the result is approximately 3.1623.
Learning objectives
Open the practice workbook
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Sample data
| Case | Input A | Input B | Broken result | Fixed result |
|---|---|---|---|---|
| Example | 10 | 0 |
Keep B2=10 and C2=0 unchanged while reproducing the error and testing the corrected formula.
Reproduce, diagnose, and fix the error
Step 1: Select D2, enter =SQRT(C2-B2), and press Enter. Because C2-B2 is 0-10=-10, D2 must display #NUM!.
=SQRT(C2-B2)Step 2: Inspect the expression inside SQRT. The source values are valid numbers, but their subtraction order produces -10. This is a numeric-domain problem, not a formatting problem.
Step 3: Select E2, enter =SQRT(B2-C2), and press Enter. B2-C2 is 10, so E2 should calculate approximately 3.16227766 while D2 remains #NUM! for comparison.
=SQRT(B2-C2)Checkpoint
| Cell | Expected |
|---|---|
| B2 | 10 |
| C2 | 0 |
| D2 | #NUM! |
| E2 | 3.16227766 |
The source inputs remain unchanged. D2 proves the negative-input failure, and E2 proves the corrected subtraction order returns a valid real-number result.
Common mistakes and fixes
If E2 still returns #NUM!, confirm B2 and C2 are numeric and verify the expression inside SQRT is nonnegative. If your real model legitimately needs square roots of negative numbers, a real-number SQRT formula is not the right operation.
Independent practice
Temporarily change B2 from 10 to 16. Verify D2 still returns #NUM! and E2 recalculates to 4, then restore B2 to 10 and confirm the checkpoint returns.
Key takeaways
- ✓#NUM! can appear when a function receives a numeric value outside its valid domain.
- ✓For this example, C2-B2 equals -10, so SQRT cannot return a real-number result.
- ✓Inspect the expression that feeds the function before adding error-handling wrappers.
- ✓Correcting the subtraction order makes the SQRT input nonnegative without changing source cells.
- ✓Test one changed input, confirm recalculation, and restore the sample before finishing.
Check your understanding
Answer four questions about the practice sheet.
Next lesson
Continue with Fix #ERROR! messages.
Comments
No comments yet. Be the first to join the discussion.