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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- multiple if statements in tableau
- if statements verilog
- how to write multiple if statements excel
- nested if statements in excel with dates
- embedded if statements in excel
- multiple if statements with dates
- multiple if statements in excel
- excel if statements with dates
- if statements using dates
- if statements with range of numbers
- if statements using dates in excel
- using if statements with dates in excel