GUIDED PRACTICE: USE IF STATEMENTS



Guided Practice: Use IF statements

In this exercise, you will follow along with your teacher to perform simple calculations, use the IF statement and fill a series.

• Record the notes and steps necessary to complete each task in the spaces provided as your teacher demonstrates.

1. Open the spreadsheet GP-IF-Student that was sent to you electronically by your teacher and save it to your files.

2. Use the Fill Series tool to input employee numbers in Column A

3. Insert a new column in front of Hours Worked and label it Overtime Rate. Format the heading in bold font and centered, with wrap text

4. In Cell E4, enter a formula to calculate the Overtime Rate (Hourly Rate x 1.5)

5. Copy the formula to the remaining cells in the column

6. Insert a column before Regular Pay and label it Overtime Hours and format appropriately

7. Insert a column before Overtime Hours and label it Regular Hours and format appropriately

8. In Column F, Hours Worked, change all occurrences of 40 to 45

9. In G4, (Regular Hours) enter an IF statement that will calculate the amount of regular hours worked and copy the formula to the remaining cells in the column.

=IF(Hours Worked40,Hours Worked-40,0)

11. In I4, calculate the Regular Pay (Hourly Rate * Regular Hours) for each employee and format the data as currency.

12. In J3, key the heading Overtime Pay in initial caps and bold font.

13. In J4, enter a formula to calculate the overtime pay (Overtime Rate * Overtime Hours) and copy the formula to the remaining cells in the column and format the data as currency.

14. In K3, enter the label Gross Pay and format it appropriately

15. Calculate Gross Pay by adding the Regular Pay and Overtime Pay in Column K

16. Check the spreadsheet to make sure all cells are formatted consistently. For example, the data in Columns I and J should have gridlines.

17. Sort the spreadsheet in descending order by Overtime Pay

18. Who are the top three overtime pay earners?

19. Sort the spreadsheet in descending order by Regular Pay

20. Who are the top three regular pay earners?

21. In A31, enter the label Total and format it appropriately

22. Using the AutoSum function, calculate the totals for regular pay, overtime pay, and gross pay. Adjust column widths if necessary

23. In A32, enter the label Averages

24. Using the average function, calculate the averages for hourly rate, overtime rate, hours worked, and gross pay. Format the cells with monetary figures as currency

25. What is the average gross pay?

26. What is the average number of hours worked? Format the cell for one decimal place.

27. What is the average hourly rate?

28. What is the average overtime rate?

29. What is the total amount for regular pay?

30. What is the total amount for overtime pay?

31. What is the total amount for gross pay?

32. In Cell K3, enter the label FICA

33. In Cell K4, enter a formula to calculate the amount of FICA tax. It is calculated by multiplying the gross pay by 7.25% (.0725). Copy the formula to all remaining cells in the column

34. In L3, enter the label Net Pay

35. In L4, enter a formula to calculate the Net Pay. It is calculated by subtracting the FICA tax from gross pay. Copy the formula to all remaining cells in the column and adjust column widths if necessary

36. Sort the spreadsheet alphabetically by Employee

37. Check the overall appearance of your spreadsheet for the following:

• Is the title centered across the entire selection?

• Are all column headings keyed in bold, centered, and is wrap text used where needed?

• Are Columns I-M set for currency?

• Are Columns D-E set for two decimal points?

• Are Columns F-H set for one decimal point?

• Did you set gridlines over the entire spreadsheet except for the average and total lines?

38. Save and submit according to teacher directions

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download