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

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