Formula 16
Operators/Functions Used
If-Then-Else, Add (+), Less than (<), Multiply (*)
Formula Purpose
A manufacturer of lawn and garden products grants a 10-5-5 discount on Category A products and 15-10-5 discount on Category B products. It also pays the freight on all orders of $5,000 or more (before discount). It charges a flat 4% freight on all orders of less than $5,000. Management wants a Total Including Freight figure to appear on the daily sales report.
Formula
If ({file.CAT A} + {file.CAT B}) < 5000 Then
1.04 * .95 *.95 *.90 *{file.CAT A} + .95 *.90 *.85
*{file.CAT B}
Else
.95 *.95 *.90 *{file.CAT A} + .95 *.90 *.85 *{file.CAT
B}
Result
| Cat A
| Cat B
| Cat A+Cat B
| TotInclFrt
|
9524
| 1344
| 10868
| 8712.62
|
3424
| 1344
| 4768
| 3908.21
|
Explanation
- This formula uses the If-Then-Else operator to say, if the sum of {file.CAT A} and {file.CAT B} is lower than $5,000 (Then), multiply the discounted price by 104% (1.04) to arrive at the price plus freight. If the sum of {file.CAT A} and {file.CAT B} is higher than $5,000 (Else), simply calculate the discounted price (no freight, since the order is bigger than $5,000.
- The If part of the expression uses the Add operator (+) to calculate the undiscounted value of {file.CAT A} and {file.CAT B}.
- It uses the Less than operator (<) to determine if the sum of {file.CAT A} and {file.CAT B} is less than $5000.
- If the sum is less than $5000, the If part of the expression is satisfied, thus triggering the Then consequence.
- The formula uses the Multiply operator (*) several times, first to multiply the value of {file.CAT A} by .90 (10% discount), to multiply that result by .95 (5% discount), and to multiply that result by .95 (5% discount) to arrive at the discounted amount for {file.CAT A}.
- It performs the same set of calculations on {file.CAT B} to determine the discounted amount for that category.
- It uses the Add operator (+) to add the discounted amounts of {file.CAT A} and {file.CAT B} to arrive at the discounted total (before freight).
- Finally, it uses the Multiply operator (*) to multiply the discounted total by 1.04 (100% + 4% freight) to arrive at the invoice total including freight.
- If the sum is $5,000 or more, the If part of the expression is not satisfied, thus triggering the Else consequence.
- The formula uses the Multiply operator (*) several times, first to multiply the value of {file.CAT A} by .90 (10% discount), to multiply that result by .95 (5% discount), and to multiply that result by .95 (5% discount) to determine the discounted amount for {file.CAT A} (freight free).
- It performs the same set of calculations on {file.CAT B} to determine the discounted amount for that category.
- Finally, it uses the Add operator (+) to add the discounted amounts of {file.CAT A} and {file.CAT B} to arrive at the final invoice total (freight free).
Related topics
Formulas In Action