115
1. Use conditi ons
in formulas to
determine a
desired result.
T
he City of Fort Worth, Texas, is changing its criteria for calculati ng raises
for city employees. Instead of everyone getti ng the same raise, the City
Council has decided to base raises on the conditi on of number of years of
service to the city. Employees who have been employed by the city for fi ve or
more years will receive a 5.5 percent raise. All other employees will receive a 4
percent raise.
The following acti vity illustrates how spreadsheets can be used to calculate
the raise percentage and raise amount, and determine each employee's
salary for 2011.
1. Create a NEW spreadsheet.
Unless otherwise stated, the font should be 10 point Arial.
2. Type the data as shown.
3. Bold rows 1 6 and row 21.
4. Format the width of columns A and B to 15.0 and left align.
5. Format the width of column C to 25.0 and left align.
6. Format the width of column D to 13.0 and center align.
7. Format the width of columns E, F, G, and H to 13.0 and right align.
8. Format cells E8 E21 and cells G8 H21 as currency displaying 2 decimal places
and the $ symbol.
9. Format cells F8 F20 as percentages displaying 2 decimal places.
10. Compute the formulas for the fi rst employee as follows:
a. In column F, % INCREASE, a conditi onal formula is required to determine if
each employee's YEARS OF SERVICE is greater than or equal to 5 years. If
the conditi on is true, the employee receives a 5.5% increase to his/her 2010
SALARY. If the conditi on is false, the employee receives a 4% increase to his/
her 2010 SALARY. The conditi onal formula for the fi rst employee to be entered
into cell F8 is, type
=IF(D8>=5, 5.5%,4%)
b. 2011 RAISE=2010 SALARY*% INCREASE In cell G8, type
=E8*F8
c. 2011 SALARY=2010 SALARY+2011 RAISE In cell H8, type
=E8+G8
11. Use the AutoFill feature to copy the formula down for the remaining
employees.
12. Enter the formulas to compute the totals for columns E, G, and H.
Raise
48
NEW SKILL
ACTIVITY
For Evaluation Purposes Only