Why this matters
A billing workbook that worked last month now shows #N/A, #VALUE!, #DIV/0! and #REF!, and the total at the bottom is an error too. The person who built it has left. Invoices go out Friday.
The fast fix is to wrap everything in IFERROR so the errors disappear. That also hides a missing rate and sends out a total that is too low. This module does it the right way: each error explained in plain words, fixed at its cause, in an order that makes sense.
Practice
Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.
What you’ll build
You are the office manager at Ridgeway Architects (made up). You attach a note describing the two sheets, the formulas and the five errors, and ask for the meaning, the cause and the right fix for each.
You get a fix order, a subtotal of $4,330.00 you can check line by line, the one row ChatGPT refused to total because the rate is missing, and a lookup formula that ignores stray spaces and shows "Add rate" for a missing project code.
What you need
- Nothing to start the practiceThe lesson is a practice copy of ChatGPT. The firm and its numbers are made up.
- A ChatGPT account, to do it for realAny plan works. You can paste the cell contents and formulas as text instead of attaching a file.
- The formula, the cells it reads and the errorCopy the formula from the formula bar and the values of the cells it points at, exactly as typed.
Concepts
What the four errors mean
#N/A: a lookup found no match. Here, once because a project code had a space after it, and once because the project was not on the Rates sheet at all. #VALUE!: math on text, here hours typed as "7.5 hrs".
#DIV/0!: dividing by zero, here because the project in the formula had no hours. #REF!: the formula points at a cell that no longer exists, here because someone deleted a column.
Fix the cause, in order
Errors flow downhill: one bad row turns the total into an error, and the summary that reads the total follows. So the order is data first (remove the space, type 7.5 as a number, add the missing rate), then the totals, then the broken link.
ChatGPT would not make up the rate for the new project: "Do not guess the rate or substitute another project's rate." Its $4,330.00 is a subtotal of the four rows it can total, and it says so.
A safer lookup, and what it still cannot do
The new formula wraps the project code in TRIM, so a pasted space no longer breaks it, and uses IFNA to show "Add rate" when the code is missing. IFNA only catches the not-found case, so other problems still show.
ChatGPT pointed out the catch itself: the Amount column will then show #VALUE! for that row, because Excel cannot multiply hours by the words "Add rate". That is the warning you want. The total stays broken until a real rate goes in.
Do it for real
Now repeat it in your own account. Tick each step as you go.
Downloads
- billing-sheet-errors.txtThe note describing the workbook and its errors. Made up.
Quick check
3 questions to check you’ve got it.
1. A lookup shows #N/A for a code that is clearly in the rate list. What is the likely cause?
- The rate is too high
- A space before or after the code, so the two do not match exactly
- The sheet is protected
Show the answer
A space before or after the code, so the two do not match exactly. Exact-match lookups treat "RW-102" and "RW-102 " as different. TRIM removes the stray space.
2. One project has no rate yet. What should the total show?
- An error or a clear flag until the real rate is entered
- The total without that row, with no warning
- A total using the average of the other rates
Show the answer
An error or a clear flag until the real rate is entered. A total that silently leaves out a row looks complete and is wrong. A visible flag gets the rate entered.
3. Which do you fix first?
- The summary sheet link
- The grand total formula
- The data in the rows the formulas read
Show the answer
The data in the rows the formulas read. Errors flow from the rows into the totals. Fix the rows and most of the rest clears by itself.
You reached the end of ChatGPT for Excel: Formulas, Cleanup and Error Fixes
You can now get a formula written, a file cleaned and an error fixed. AI for Excel and Google Sheets goes inside the spreadsheet: budgets, PivotTables and AI in the cells.
Pick your next course →