Purpose
By the end of this lesson, you will be able to give an AI enough information to correctly diagnose and fix a broken formula.
Lesson Explanation
Debugging requests need the exact broken formula, the exact error or wrong result it produces, and – critically – a specific example row of real (or realistic sample) data the formula is failing on, since many formula bugs only appear with certain data patterns.
A genuinely useful debugging prompt distinguishes between “the formula produces an error” and “the formula runs without error but gives the wrong number” – these are different categories of problem requiring different diagnostic approaches, and conflating them wastes a round of back-and-forth.
If the formula was recently working and only broke after a specific change (a new row added, a column inserted, data reformatted), mentioning that change is often the single most useful diagnostic clue, since it points directly at what’s different now versus when the formula worked.
After receiving a suggested fix, testing it against the SAME example data that was originally failing – not just a fresh assumption that it works – closes the loop and confirms the actual problem was solved, not just a plausible-sounding but untested new formula produced.
Practice Questions
1. A user tells an AI “my formula is broken, please fix it” with no formula pasted and no description of the actual problem. What three things does this lesson say a debugging request needs?
View Answer
The exact broken formula, the exact error or wrong result produced, and a specific example row of data the formula is failing on.
2. A formula produces the error #N/A, and a different formula elsewhere produces a plausible-looking but factually incorrect number with no error shown. Why does this lesson say these need to be described differently?
View Answer
These are genuinely different categories of problem (an outright error versus a silently wrong result) requiring different diagnostic approaches, and describing them the same way could lead to the wrong kind of troubleshooting.
3. A formula that worked correctly for months suddenly breaks the same day a new column was inserted into the dataset. What single piece of information would most usefully help an AI diagnose this?
View Answer
Mentioning that a column was recently inserted, since this is often the single most useful clue pointing directly at what changed.
4. Write a well-formed debugging request: a VLOOKUP formula =VLOOKUP(A2,B:D,3,FALSE) is returning #N/A for a specific value, “Acme Corp,” that the user can visually confirm exists in column B.
View Answer
Something like: “This formula, =VLOOKUP(A2,B:D,3,FALSE), returns #N/A for A2 containing ‘Acme Corp,’ even though I can see ‘Acme Corp’ in column B row 15. Why might this be happening?”
5. After an AI suggests a fixed version of a broken formula, what does this lesson recommend doing before considering the problem solved?
View Answer
Testing the fix against the same example data that was originally failing, rather than assuming a plausible-sounding suggestion definitely works.
6. A formula runs without any error message but returns a number that’s clearly too low compared to a manual calculation. Is this an “error” or a “wrong result” problem, per this lesson’s distinction?
View Answer
A wrong result problem – no error is thrown, but the calculation itself is producing an incorrect value.
7. A user reports “the formula stopped working” without specifying whether this means a new error appeared or the results just started looking wrong. Why might this ambiguity slow down getting a useful diagnosis?
View Answer
Without knowing which of the two genuinely different problem categories this is, the AI cannot target its diagnostic questions or suggestions appropriately, likely requiring an extra round of clarification.
8. A formula previously worked, but recently the underlying data was reformatted from text-based dates to true date values. If a date-based formula started acting strangely after this change, what should the debugging request mention?
View Answer
That the date format was recently changed, since this specific recent change is a strong diagnostic clue pointing toward the likely cause.
9. An AI suggests a formula fix, and the user applies it to the whole spreadsheet without first testing it against the specific failing example. What risk does this create, per this lesson’s guidance?
View Answer
The fix might not have actually solved the original problem (or might have introduced a new one), and applying it broadly without first confirming it works on the known failing case risks spreading an unverified fix.
10. A debugging request includes the broken formula, the exact error code shown, and one example row where it fails – but doesn’t mention that this row has an unusually formatted date. Why might this omission matter?
View Answer
If the actual bug is related to date formatting, omitting this detail removes a clue that could have pointed directly at the real cause, potentially leading to an incorrect diagnosis.
11. Why does providing “a specific example row of real or realistic sample data” matter more than just describing the problem in general terms?
View Answer
Many formula bugs only appear with certain specific data patterns (like a particular text format or edge case), so a general description without a concrete example may not capture the actual trigger for the bug.
12. A user asks an AI to fix a formula and, once given a suggestion, immediately shares it with a whole team without personally verifying it against the original failing case first. What does this lesson suggest about this practice?
View Answer
The fix should be tested against the original failing example first to confirm it actually works, rather than trusting and distributing an unverified suggestion.