### Formula 4

#### Operators/Functions Used

Nested formulas, If-Then-Else, Subtract (-), Multiply (*), Greater than (>), Greater than or equal (>=), Sum, Parentheses ()

#### Formula Purpose

A computer store sells hardware, software, and books. It pays its sales representatives 7% of all hardware sales (monthly) over $5000, 10% of all software sales (monthly) over $10, 000, and 5% of all book sales (monthly) over $1000. The sales manager wants to calculate the commission in each category for each representative, total the commission due each representative, and flag those representatives who are entitled to more than $5000 in total commission for the month. We will do this using nested formulas, that is, using one formula as one of the elements in another formula.

#### Formula(s)

#### @HARDWARE

    If (({file.HARD SALES}-5000)> 0) Then

        .07 * ({file.HARD SALES}-5000)

    Else

        0

#### @SOFTWARE

    If (({file.SOFT SALES}-10000)> 0) Then

        .10 * ({file.SOFT SALES}-10000)

    Else

        0

#### @BOOKS

    If (({file.BOOK SALES}-1000)> 0) Then

        .05 * ({file.BOOK SALES}-1000)

    Else

        0

#### @TOTCOMM

    Sum([{@HARDWARE}, {@SOFTWARE}, {@BOOKS}])

#### @FLAG

    If {@TOTCOMM} >= 5000 Then

        " **** "

    Else

        ""

#### Result

Given this data:

Salesrep | HardSales | SoftSales | BookSales
Salesrep A | 4500 | 21000 | 985
Salesrep B | 31427 | 41222 | 4470
Salesrep C | 22000 | 4687 | 4250
Salesrep D | 14000 | 15678 | 2200

The formulas return the following results:

Salesrep | Hardware | Software | Books | Total | Flag
SalesrepA | 0.00 | 1100.00 | 0.00 | 1100.00 |
SalesrepB | 3122.20 | 1849.89 | 173.50 | 5145.59 | ****
SalesrepC | 1190.00 | 0.00 | 162.50 | 1352.50 |
SalesrepD | 630.00 | 567.80 | 60.00 | 1257.80 |

#### Explanation

- The three formulas (@HARDWARE, @SOFTWARE, and @BOOKS) work in the same manner.
- They use the If-Then-Else operator to test for a condition, do one thing if the condition is true, do another thing if the condition is false.
- · They take total sales in the category, use the Subtract operator (-) to subtract the amount of sales on which no commission is to be paid, and test to see if the remaining amount is a positive number (>0).
- · If it is (>0), they calculate the commissions using the Multiply operator (*) to multiply the appropriate commission percentage (expressed as a decimal: .07, .10, .05) times the commissionable amount ({file.HARD SALES}-5000}, etc.).
- · If the remaining amount is a negative number (<0), no commission is computed and the formula prints a zero amount (0.00).
- · @TOTCOMM uses the Sum function to total the commissions due. Instead of including the calculations for each formula (a duplication of time and effort) it substitutes the formula name instead of the calculations. When the program encounters the formula name it knows to use the underlying calculations from the formula(s) referenced.
- · @FLAG uses the If-Then-Else operator to evaluate the values calculated by @TOTCOM and to flag (****) those values of $5000 or more (>= 5000). @FLAG effectively nests two levels of other formulas: it nests @TOTCOM, which is a formula that itself nests three other formulas, @HARDWARE, @SOFTWARE, and @BOOKS. When the program sees @TOTCOM, it performs all of the underlying calculations referenced by that formula and the formulas that are used in @TOTCOM.
- · Parentheses () are used throughout the formulas to control the order of calculation.

###### Related topics

Formulas In Action

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