Simplify a complex formula with LET
AdvancedLong formulas are harder to review when the same business calculation appears over and over. LET solves that by letting you name each important step once, which makes pricing and commission logic much easier to audit in real work.
In this sheet, column F already contains the correct legacy commission formula for each rep, but it repeats the same net sales calculation several times. Your job is to rewrite that logic in G2:G11 so each formula is easier to read and maintain.
The business policy is the same in every row:
- Net sales are units sold times unit price, minus returns or credits, but never below zero
- If net sales are under
$1,000, commission is0 - Otherwise, use a segment rate of
12%forEnterprise,8%forGrowth, and5%forCore - Add a
$500bonus when net sales reach at least$10,000 - Round the final commission to
2decimals
Refactor the logic in G2 with LET, then fill the pattern down through G11. Use the existing formula in F2:F11 and the check values in H2:H11 to confirm your rewritten version returns the same commission in every row.
Need some help?
Hint 1
The repeated expression to name first is the net sales calculation after returns or credits, because the legacy formula uses it in more than one rule check.
Hint 2
After you name the repeated net sales calculation, use additional LET names only where they make the commission policy easier to audit. The finished formula still needs to handle the segment rate, the `$10,000` bonus rule, and the final rounding.
Simplify a complex formula with LET
AdvancedLong formulas are harder to review when the same business calculation appears over and over. LET solves that by letting you name each important step once, which makes pricing and commission logic much easier to audit in real work.
In this sheet, column F already contains the correct legacy commission formula for each rep, but it repeats the same net sales calculation several times. Your job is to rewrite that logic in G2:G11 so each formula is easier to read and maintain.
The business policy is the same in every row:
- Net sales are units sold times unit price, minus returns or credits, but never below zero
- If net sales are under
$1,000, commission is0 - Otherwise, use a segment rate of
12%forEnterprise,8%forGrowth, and5%forCore - Add a
$500bonus when net sales reach at least$10,000 - Round the final commission to
2decimals
Refactor the logic in G2 with LET, then fill the pattern down through G11. Use the existing formula in F2:F11 and the check values in H2:H11 to confirm your rewritten version returns the same commission in every row.
Need some help?
Hint 1
The repeated expression to name first is the net sales calculation after returns or credits, because the legacy formula uses it in more than one rule check.
Hint 2
After you name the repeated net sales calculation, use additional LET names only where they make the commission policy easier to audit. The finished formula still needs to handle the segment rate, the `$10,000` bonus rule, and the final rounding.