Purpose

Use this lesson to draft formulas and explanations while verifying them in the actual spreadsheet. The aim is to produce work that a colleague can review and act on, not merely text that sounds convincing.

Lesson Explanation

Scenario: Calculate a 10% discount only when column C says Approved.

Better instruction: Suggest an Excel formula that applies a 10% discount to B2 when C2 equals “Approved”; explain how to test it with one approved and one unapproved row.

AI can propose formulas, but it does not see your workbook unless you provide the structure. Name cell references, the expected behavior, and test cases. Run the formula in the spreadsheet and check edge cases such as blanks. The formula behaves differently for Approved and unapproved rows and is tested in the workbook.

Work in a short cycle: provide the relevant input, state the result and limits, inspect the draft against the acceptance criteria, then revise the instruction when a requirement is missing. Keep the final human decision with the person responsible for the work.

Practice Questions

1. A colleague asks an AI to “write a formula to flag late orders” without naming any specific cell references. What does this lesson say about this kind of request?

   View Answer

   AI can propose formulas, but it does not see your workbook unless you provide the structure – cell references and expected behavior need to be named.

2. What does this lesson recommend doing after an AI proposes a formula, before trusting it?

   View Answer

   Run the formula in the spreadsheet and check edge cases such as blanks.

3. Write a prompt for a formula that marks a row “Overdue” when a date in column D is more than 30 days in the past, following this lesson’s guidance.

   View Answer

   Something like: “Suggest an Excel formula that returns ‘Overdue’ in E2 when the date in D2 is more than 30 days before today; explain how to test it with a recent date and an old date.”

4. An AI-suggested formula works correctly for populated rows but produces an error for blank cells. What does this lesson say about testing for this kind of case?

   View Answer

   Edge cases like blanks should specifically be checked when testing a formula in the spreadsheet.

5. A prompt for a formula names the exact cell references, the expected behavior, and asks for test cases, but the person never actually runs the formula before using it. What step from this lesson was skipped?

   View Answer

   Running the formula in the spreadsheet and checking edge cases – verification, not just requesting the formula.

6. Why does this lesson say AI “does not see your workbook unless you provide the structure”?

   View Answer

   The AI has no direct access to the actual spreadsheet’s layout or contents, so cell references and expected behavior must be explicitly described in the prompt.

7. A formula prompt asks for a discount to apply only when a status column says “Approved,” and requests test cases with both an approved and unapproved row. What principle does this reflect?

   View Answer

   Naming cell references, expected behavior, and test cases, consistent with this lesson’s guidance.

8. An AI-suggested formula appears correct on a quick glance but hasn’t actually been tested in the real spreadsheet. What risk does this lesson identify with skipping this step?

   View Answer

   The formula might behave unexpectedly for cases not considered on a quick glance, such as blank cells or edge cases, which testing would catch.

9. A request for a spreadsheet formula doesn’t specify which cell should contain the input value being checked. What is missing, per this lesson’s guidance?

   View Answer

   Named cell references.

10. Why does this lesson specifically recommend requesting test cases alongside the formula itself?

    View Answer

    Test cases (like one approved and one unapproved row) allow direct verification that the formula behaves correctly before relying on it, rather than trusting it on faith.

11. A team member receives a formula suggestion, tests it against one normal case, and immediately deploys it without testing a blank or edge case. What does this lesson suggest was incomplete about this verification?

    View Answer

    Edge cases specifically (like blanks) should be checked, not just a single normal case.

12. A prompt for a spreadsheet formula specifies the exact cell (B2), the condition (C2 = “Approved”), the expected output, and asks for a test with both an approved and unapproved row. What does this reflect?

    View Answer

    A well-formed prompt following this lesson’s full guidance: named references, expected behavior, and test cases.