### 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

Seagate Software, Inc. http://www.seagatesoftware.com Please send comments to: techpubs@seagatesoftware.com