Library › Data AI › Finance and accounting

Write a formula for goal attainment capped at 100 percent

  • For Finance and accounting
  • You practice Setting limits and rules
  • Time about 5 minutes
  • Level Intermediate

About this exercise

Your bonus sheet needs goal attainment in D2 from sales in B2 and goal in C2. Ask for one Excel formula in a single code block, then one line starting 'Example:' showing sales 120 against a goal of 100, under 12 words outside the code. Attainment is capped at 100% for bonus purposes, and a missing or zero goal should show the text n/a.

Common mistakes when you ask AI for this

  • Letting it exceed 100%. The AI returns 120% for the over-goal rep. Try: Add: "Cap at 100%: MIN(1, B2/C2)."
  • #DIV/0! errors. Zero or blank goals produce errors. Try: Add: "If C2 is blank or 0, return n/a."
  • Wrong Excel version. The AI uses IFS, which Excel 2016 lacks. Try: Add: "No IFS, XLOOKUP or LET."
  • Leaving the format to chance. The AI picks its own length and layout unless told the exact shape. Try: Add: "Put the code in exactly 1 code block. Keep the writing outside the code under 12 words."

What a strong prompt includes

  • Asks for the cap
  • Handles the missing goal
  • Names the Excel version
  • Asks for the example
  • Limits the output

Practice this now ↑

Related exercises