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