Logical Functions

Spreadsheet Decision Making

Logical Functions

Open the file Commission Calculator. Make the following changes. Points for each item are in square brackets. You must use absolute cell references where appropriate.

·  [2] Put a formula in the Total Sales column that will add the four sales values for each salesperson (columns B, C, D, and E).

·  [2] Put a formula in the Regular Commission column that multiplies the total sales (column F) by the Regular Commission Rate (F2).

·  [4] In the four bonus columns (Q1 Bonus, Q2 Bonus, Q3 Bonus, and Q4 Bonus), compute the bonus. A bonus is earned if the sales for that quarter met or exceeded the Quarterly Quota (at the top of the spreadsheet). If the quota was met, the bonus is computed by multiplying the Bonus Commission Rate by the amount by which the quota was exceeded. If the quota is not met, the bonus is 0. For example, the Q1 Bonus for Beetle is ($400,000 - $250,000) * 2.00% = $3,000.

·  [2] Put a formula in the Total Bonus column that adds the bonuses for Q1 through Q4 (columns H, I, J, and K).

·  [2] In the All 4? column, put the value TRUE if the sales for all four quarters met or exceeded the quota. Put the value FALSE otherwise. This does NOT require an IF function.

·  [2] In the $1.25M column, put the value TRUE if the Total Sales for the salesman was $1,250,000 or more. Put the value FALSE otherwise. This does NOT require an IF function.

·  [2] In the All 4 or $1.25M column, put the value TRUE if either the value in the All 4 column is TRUE or the value in the $1.25M column is TRUE.

·  [2] In the Extra Bonus column: if the value in the All 4 or 1.25M? column is TRUE, put the amount in F4. Put $0 otherwise. Note that $0 will be displayed as a hyphen.

·  [2] In the Total Pay column: Add the Regular Commission (column G) plus the Total Bonus (column L) plus the Extra Bonus (column P).