### Formula 12

#### Operators/Functions Used

If-Then-Else, Average([array]), Subtract (-), Greater than (>), Minimum([array]), Multiply (*), Parentheses ().

#### Formula Purpose

A consultant has contracts that pay him a percentage of the amount he saves his clients with a cap or ceiling on the amount he can earn. His contracts pay him 25% of the monthly savings up to a maximum of $10,000 per month. If his percentage amounts to more than $10,000, he collects the $10,000 maximum; if the percentage is less than $10,000, he collects the actual percentage. The base period against which savings are calculated is the average of three typical months in a prior year.

#### Formula

    If (Average([{file.MON1}, {file.MON2}, {file.MON3}]) -
    {file.CURRENTMON})> 0 Then
    Minimum([.25*((Average([{file.MON1}, {file.MON2},
    {file.MON3}) - {file.CURRENTMON}]), 10000])
    Else
    0

#### Result

Average | CurMon | Savings | 25% | Amt.Due
90, 000 | 65, 000 | 25, 000 | 6250 | 6250
120, 000 | 60, 000 | 60, 000 | 15, 000 | 10, 000
75, 000 | 77, 000 | N/A | N/A | 0

#### Explanation

- The formula uses the If-Then-Else operator to say: if the average outlay during the three months specified was higher than the current month, then return 25% of the difference, up to a maximum of $10,000. If the average outlay was equal to or less than the outlay during the current month, return 0.
- The formula uses the Average function to calculate the average outlay during {file.MON1}, {file.MON2}, and {file.MON3} - the baseline period.
- It uses the Subtract operator (-) to subtract the outlay in the current month from the average during the baseline period, thus giving the difference between the current month and the baseline.
- It uses the Greater than operator (>) to compare that difference to 0.
- If the difference is greater than 0 (there actually was a savings), the If part of the expression is satisfied, thus triggering the Then consequences.
- If the Then consequences are triggered:
- The formula uses the Average function to calculate the average outlay during {file.MON1}, {file.MON2}, and {file.MON3} - the baseline period.
- · It uses the Subtract operator (-) to subtract the outlay in the current month from the average during the baseline period.
- · It uses the Multiply operator (*) to calculate 25% of the difference (.25 *...).
- · It uses the Minimum ([array]) function to return the smaller amount: either 25% of the difference, or $10,000. Using the Minimum function in this way effectively puts a ceiling on the result of the calculation.
- · If the difference is equal to or less than 0 (there was no savings), the If part of the expression is not satisfied, thus triggering the Else consequence.
- · The Else consequence (Else 0) simply returns the value 0.

###### Related topics

Formulas In Action

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