Library › Data AI › Finance and accounting

Write a nested IF for tiered commission

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

About this exercise

Sales ops needs commission percent in column C from the deal amount in column B. Ask for one Excel formula for C2 in a single code block, then one line starting 'Tiers:' repeating the three tiers in under 20 words. Your Excel is 2016, so IFS isn't available, and a blank amount must give 0.

Common mistakes when you ask AI for this

  • Using IFS. The AI picks IFS, which Excel 2016 doesn't have. Try: Add: "Excel 2016: nested IF only, no IFS or SWITCH."
  • Wrong boundaries. The AI uses > instead of >= so $50,000 earns 0%. Try: Add: "Use >= for the boundaries: 50,000 gives 3%, 100,000 gives 5%."
  • Missing the tiers line. The AI explains in a paragraph instead of one line. Try: Add: "After the code, one line: Tiers: ... in under 20 words."
  • 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 30 words."

What a strong prompt includes

  • Says nested IF
  • Names the Excel version
  • Gives the boundaries
  • Handles blanks
  • Fixes the format

Practice this now ↑

Related exercises