求助：Google Sheets 销售团队阶梯式薪资计算公式

Let's map out your salary rules clearly first, then build the exact formula you need:

Salary Rules Recap #

• Base Pay Tier: You get the higher of either a £350 guaranteed "safety net" salary, or 40% commission on the first £2000 of sales. (Note: £875 in sales is the break-even point here—40% of £875 equals £350, so the safety net kicks in automatically when sales are below this amount.)
• Excess Sales Tier: Any sales above £2000 earn a 50% commission rate.

The Google Sheets Formula #

Assume your total sales figure is in cell A1. Use this formula to calculate the salary:

=MAX(350, MIN(A1, 2000)*0.4) + MAX(0, A1-2000)*0.5

Breakdown of the Formula #

Let's break down what each part does to make it easier to understand:

1. MAX(350, MIN(A1, 2000)*0.4): Handles the base pay calculation.
   • MIN(A1, 2000) caps sales at £2000 for the 40% commission tier.
   • Multiply that by 0.4 to get the 40% commission amount.
   • MAX(350, ...) ensures we pick whichever is higher: the £350 safety net or the 40% commission on up to £2000.
2. MAX(0, A1-2000)*0.5: Handles the excess sales commission.
   • A1-2000 calculates how much sales exceed £2000.
   • MAX(0, ...) makes sure we don't add a negative number if sales are below £2000.
   • Multiply by 0.5 to get the 50% commission on the excess.

Verification with Your Examples #

Let's test this formula against your sample sales figures to confirm it works:

• Sales = £500: MAX(350, 500*0.4) + 0 = MAX(350,200) = £350 ✔️
• Sales = £900: MAX(350,900*0.4) +0 = MAX(350,360)=£360 ✔️
• Sales = £1500: MAX(350,1500*0.4)+0=MAX(350,600)=£600 ✔️
• Sales = £2500: MAX(350,2000*0.4) + (2500-2000)*0.5 = 800 +250=£1050 ✔️
• Sales = £4000: MAX(350,2000*0.4)+(4000-2000)*0.5=800+1000=£1800 ✔️

内容的提问来源于stack exchange，提问作者Mark Fitzpatrick