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