The purpose of this assignment is to:
1. Apply the AND formula
2. Apply the IF formula
3. Use argument boxes successfully
Preparation: (Review these terms)
- Insert extra rows
- Change font and size
- Adjust columns as needed
New Terms: (Terms are bolded in the steps in the assignment below)
- Apply the AND function (Step 12, page 2)
- Apply the IF function (Step 13, page 2)
It is one thing to understand a formula and quite another to apply that formula. This exercise allows you to practice the If and AND functions and to view the results. (THIS ASSIGNMENT IS WORTH 20 POINTS. THE RUBRIC IS ON PAGE 2.)
East End Building Materials Assignment
1. Open the spreadsheet called East End Building Materials and save it as East End.
2. Center the title (East End Building Materials) over the entire table and change the font to 14 Aharoni,
3. Center the sub title (Bonus Pay Summary) over the entire table and change the font to 12 pt Aharoni.
4. Insert an extra row below the sub title but above the column headings. In that row, type your name and center it. Choose the font and size you would like
5. Select the Title, Sub Title, Your Name and the column headings (entire rows of each) and shade them in light gray.
6. With that section still highlighted, place a border underneath the column headings.
7. Select the column headings and bold them.
8. Adjust the columns as necessary
9. Adjust the Performance Rating column heading using the Wrap Text so the heading is on two lines.
10. Adjust the Bonus Amount heading to display two lines
11. Select the numbers in the Performance Rating column and center those numbers within the column.
12. In Cell E6 insert an AND formula that will determine if the employee is eligible for a bonus. An employee is eligible if Sales exceeded Quota AND if the performance rating was greater than 6. (Hint: The end result should only be three TRUE’s once the formula is pulled down.)
13. To figure the bonus amount for those employees who ARE eligible, use all three boxes of IF formula. In the first box, response in the Eligibility should be equal True (no quotation marks needed). If true, then the second box should be a calculation of two percent of the sales amount. If the statement is FALSE, (third box) the bonus amount is 0. (Hint: Only those employees with True in the Eligible column should have Bonus Amount, the rest are 0.)
14. Save this worksheet as East End.
15. Submit this worksheet in the East End Submit box.