Formula 7
Operators/Functions Used
If-Then-Else, Subtract (-), Greater than (>), Maximum ([array]), Multiply (*), Parentheses ().
Formula Purpose
A company has the following bonus/commission structure for its sales force: when a sales representative sells at or over quota, the representative earns a $250 bonus or a 15% commission (on the amount over quota), whichever is highest. The company pays no bonus or commission on sales less than quota. The sales manager wants bonus/commission calculated and included on a report.
Formula
If ({file.SALES}-{file.QUOTA})>=0 Then
Maximum([250,.15*({file.SALES} - {file.QUOTA})])
Else
0
Result
| Sales
| Quota
| 15%
| Amount Paid
|
10,000
| 8000
| 300
| 540 (calculated commission)
|
8000
| 8000
| 0
| 250 (guaranteed bonus)
|
7999
| 8000
| 0
| 0 (sales not >= Quota)
|
Explanation
- This formula uses the If-Then-Else operator to test sales to see if they are greater than or equal to quota.
- If they are (Then) it calculates the commission and pays the higher of commission or guaranteed bonus.
- If they are not (Else), it does nothing.
- To test sales to see if they are at least equal to quota, the formula uses the Subtract operator (-) to subtract {file.QUOTA} from {file.SALES}.
- If {file.SALES} is greater than or equal to {file.QUOTA}, this subtraction will produce the result of zero or a positive number (>=0). This will trigger the Then part of the formula.
- If {file.SALES} is less than {file.QUOTA}, this subtraction will produce a negative result. This will trigger the Else part of the formula.
- The Then part of the formula uses the Multiply operator (*) to compute a 15% commission on the amount of sales in excess of quota: .15 * ({file.SALES} - {file.QUOTA}).
- It then uses the Maximum([array]) function to determine if the calculated commission or the guaranteed bonus of $250 offers the highest payout Maximum([commission, 250]), and it returns this highest value.
- Calculating this highest payout is the bottom line of the Then part of the formula.
- The Else part of the formula makes no calculations and returns 0.
Related topics
Formulas In Action