Project Description:
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34. 
0.000 
2 
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead. 
9.000 
3 
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values. 
9.000 
4 
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee. 
6.000 
5 
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately. 
6.000 
6 
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately. 
10.000 
7 
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately. 
6.000 
8 
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee. 
6.000 
9 
Copy the formulas in the range E5:K5 to the range E6:K16. 
6.000 
10 
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17. 
8.000 
11 
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17. 
6.000 
12 
Apply Comma Style to the monetary values for the range E6:K16. 
4.000 
13 
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals. 
6.000 
14 
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23). 
10.000 
15 
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document. 
3.000 
16 
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side. 
5.000 
17 
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 
0.000 

Total Points 
100.000 
Project Description:
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34. 
0.000 
2 
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead. 
9.000 
3 
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values. 
9.000 
4 
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee. 
6.000 
5 
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately. 
6.000 
6 
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately. 
10.000 
7 
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately. 
6.000 
8 
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee. 
6.000 
9 
Copy the formulas in the range E5:K5 to the range E6:K16. 
6.000 
10 
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17. 
8.000 
11 
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17. 
6.000 
12 
Apply Comma Style to the monetary values for the range E6:K16. 
4.000 
13 
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals. 
6.000 
14 
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23). 
10.000 
15 
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document. 
3.000 
16 
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side. 
5.000 
17 
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 
0.000 

Total Points 
100.000 
Project Description:
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34. 
0.000 
2 
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead. 
9.000 
3 
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values. 
9.000 
4 
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee. 
6.000 
5 
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately. 
6.000 
6 
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately. 
10.000 
7 
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately. 
6.000 
8 
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee. 
6.000 
9 
Copy the formulas in the range E5:K5 to the range E6:K16. 
6.000 
10 
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17. 
8.000 
11 
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17. 
6.000 
12 
Apply Comma Style to the monetary values for the range E6:K16. 
4.000 
13 
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals. 
6.000 
14 
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23). 
10.000 
15 
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document. 
3.000 
16 
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side. 
5.000 
17 
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 
0.000 

Total Points 
100.000 
Project Description:
Project Description:Project Description:Project Description:
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.
Instructions:
Instructions:Instructions:Instructions:
For the purpose of grading the project you are required to perform the following tasks:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34. 
0.000 
2 
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead. 
9.000 
3 
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values. 
9.000 
4 
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee. 
6.000 
5 
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately. 
6.000 
6 
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately. 
10.000 
7 
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately. 
6.000 
8 
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee. 
6.000 
9 
Copy the formulas in the range E5:K5 to the range E6:K16. 
6.000 
10 
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17. 
8.000 
11 
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17. 
6.000 
12 
Apply Comma Style to the monetary values for the range E6:K16. 
4.000 
13 
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals. 
6.000 
14 
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23). 
10.000 
15 
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document. 
3.000 
16 
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side. 
5.000 
17 
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 
0.000 

Total Points 
100.000 
Step
Instructions
Points Possible
Step
Instructions
Points Possible
Step
Step
StepStepStep
Instructions
Instructions
InstructionsInstructionsInstructions
Points Possible
Points Possible
Points PossiblePoints PossiblePoints Possible
1
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34.
0.000
2
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead.
9.000
3
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values.
9.000
4
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee.
6.000
5
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately.
6.000
6
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.
10.000
7
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately.
6.000
8
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee.
6.000
9
Copy the formulas in the range E5:K5 to the range E6:K16.
6.000
10
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17.
8.000
11
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17.
6.000
12
Apply Comma Style to the monetary values for the range E6:K16.
4.000
13
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals.
6.000
14
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23).
10.000
15
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document.
3.000
16
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
5.000
17
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
100.000
1
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34.
0.000
1
1
111
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34.
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34.
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34.exploring_e02_grader_h2.xlsx
0.000
0.000
0.000
2
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead.
9.000
2
2
222
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead.
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead.
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead.
9.000
9.000
9.000
3
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values.
9.000
3
3
333
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values.
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values.
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values.0
9.000
9.000
9.000
4
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee.
6.000
4
4
444
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee.
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee.
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee.
6.000
6.000
6.000
5
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately.
6.000
5
5
555
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately.
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately.
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately.
6.000
6.000
6.000
6
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.
10.000
6
6
666
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.
10.000
10.000
10.000
7
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately.
6.000
7
7
777
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately.
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately.
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately.
6.000
6.000
6.000
8
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee.
6.000
8
8
888
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee.
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee.
Calculate the net pay in cell K5 by subtracting the taxes from the gross pay for the first employee.
6.000
6.000
6.000
9
Copy the formulas in the range E5:K5 to the range E6:K16.
6.000
9
9
999
Copy the formulas in the range E5:K5 to the range E6:K16.
Copy the formulas in the range E5:K5 to the range E6:K16.
Copy the formulas in the range E5:K5 to the range E6:K16.
6.000
6.000
6.000
10
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17.
8.000
10
10
101010
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17.
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17.
Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay in the respective cells within the range E17:K17.
8.000
8.000
8.000
11
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17.
6.000
11
11
111111
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17.
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17.
Apply Accounting Number Format to the ranges C5:C16, E5:K5, and E17:K17.
6.000
6.000
6.000
12
Apply Comma Style to the monetary values for the range E6:K16.
4.000
12
12
121212
Apply Comma Style to the monetary values for the range E6:K16.
Apply Comma Style to the monetary values for the range E6:K16.
Apply Comma Style to the monetary values for the range E6:K16.
4.000
4.000
4.000
13
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals.
6.000
13
13
131313
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals.
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals.
Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals.Underline the last employee’s monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals.
6.000
6.000
6.000
14
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23).
10.000
14
14
141414
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23).
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23).
Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range I21:K23).
10.000
10.000
10.000
15
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document.
3.000
15
15
151515
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document.
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document.
You want to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours, and over 40 hours. Cells J28:J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the FREQUENCY function in the range K28:K31. The functions should identify one employee who worked between 0 and 19 hours and six employees who worked more than 40 hours. Use Help to learn about the FREQUENCY function, download the sample practice data to learn how to use the function, and then close the practice document.
3.000
3.000
3.000
16
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
5.000
16
16
161616
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
Create a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.Exploring Series
5.000
5.000
5.000
17
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
17
17
171717
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
0.000
0.000
Total Points
100.000
Total Points
Total Points
Total PointsTotal PointsTotal Points
100.000
100.000
100.000100.000100.000