Working with Advanced Functions

December 2, 2018AdminUncategorized

Excel – Tutorial 8

Working with Advanced Functions (Application Assignmet)

Vanessa wants you to analyze the MBHC employee data to determine each employee’s work anniversary, available comp days, and bonus eligibility. She also wants you to calculate the bonus amount for eligible employees. Complete the following:

Open the Employees workbook downloaded from Blackboard, and save the workbook as MBHC Employees in your CS245 folder you created.

In the Documentationworksheet, enter your name and the date.

In the Employee Data worksheet, rename the Excel table as EmployeeTbl.

Design > Properties > Table Name:EmployeeTbl

In the Work Anniversary column, enter an IF function. If the month in the employee’s hire date matches the month in the Data Tables worksheet (cell B4), Yesshould appear in the work anniversary column; otherwise, the cell should remain blank. (Hint Remember to use an absolute cell reference to cell B4 because the formula will be copied to the rest of the column.) All employees receive a card on their work anniversary.

Select cell L2 >Formulas >Function Library >Logical > IFERROR >

Value: IF(MONTH(E2)=’Data Tables’!$B$4,”Yes”,””)
Value_if_error: “”> OK

In the Eligible for Bonus column, enter a formula with IF and OR functions to display the text No if the employee’s pay grade is D or the employee’s job status is a part time (PT). Leave the cell blank if the employee is eligible for a bonus.

Select cell M2 >Formulas >Function Library >Logical > IF >

Logical_test: OR(D2=”D”,J2=”PT”) >Value_if_true: “No”
Value_if_false: “”> OK

In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the Data Tables worksheet. (Hint: Remember to use absolute references to the cells in the Data Tables worksheet because the formula will be copied to the entire column.)

Select cell N2 >Formulas>Function Library >Logical > IF >

Logical_test: [Years of Service]>10
Value_if_true: ‘Data Tables’!$B$11
Value_if_false: IF([Years of Service]>5,’Data Tables’!$B$10,

IF([Years of Service]>1,

‘Data Tables’!$B$9,

‘Data Tables’!$B$8))

> OK

In the Bonus column, enter an IF function with a nested VLOOKUP function to calculate the bonus for each employee based on whether the employee is eligible for a bonus (column M) and his or her Pay Grade. The bonus information for qualifying employees is in a table named Bonus_Table in the Data Tables worksheet. Format the Bonus column with the Accounting format with no decimal places.

Select cell O2 >Formulas >Function Library >Logical > IF >

Logical_test: [Eligible for Bonus] = “”
Value_if_true: VLOOKUP([Pay Grade],Bonus_Table,2,0)
Value_if_false: 0> OK

Select Bonuscolumn> Home > Number > General (click down arrow) > Accounting > Click Decrease Decimal twice

In the Years of Service column, modify the formula to include the IFERROR function and display the message Invalid hire date if an error value occurs. Test the modified formula by changing the date in cell E2 from 7/10/2014 to 17/10/2014. AutoFit the column.

Select cell F2 >Formulas >Function Library >Logical > IFERROR >

Value: (‘Data Tables’!$B$3-E2)/365
Value_if_error: “Invalid hire date”> OK

In the Work Anniversary column, which also uses the Hire Date modify the formula to include the IFERROR function and display the message Invalid hire date if an error value occurs. AutoFit the column.

Select cell L2 >Formulas >Function Library >Insert Function> IFERROR >Value_if_error: “Invalid hire date” > OK

Edit the Duplicate Values conditional formatting rule applied to theEmployee IDcolumn so that the fill color of the duplicate value is formatted as light blue (the seventh color in the bottom row of the Background Color palette). Test this change by typing 3226 in cell A101.

Select cell A2 >Home > Styles >Conditional Formatting >Manage Rules >Edit Rules >

Format >Fill>Background Color> Select Light Blue > OK> OK > OK

In the Employee Analysis worksheet, enter the COUNTIF function in cells B3 and B4 to count the number of part-time and full-time employees, respectively (Hint: Count the job Status column in the EmployeeThl table.)

Select Cell B3 > Formulas > Function Library > More Function (click down arrow) >Statistical (click down arrow)>COUNTIF >

Range: EmployeeTbl[Job Status]
Criteria: A3 > OK

Select Cell B4> Formulas > Function Library > More Function (click down arrow) > Statistical (click down arrow) >COUNTIF >

Range: EmployeeTbl[Job Status]
Criteria: A4> OK

In cells C3 and C4, enter the SUMIF function to calculate the total salaries of part-time employees and the total salaries of full-time employees, respectively. Format the Total Salary column with the Accounting format and no decimal places.

Select Cell C3 > Formulas > Function Library > More Function (click down arrow) > Statistical (click down arrow) > SUMIF >

Range: EmployeeTbl[Job Status]
Criteria: A3
Sum_range: EmployeeTbl[Current Salary]> OK

Select Cell C4> Formulas > Function Library > More Function (click down arrow) > Statistical (click down arrow) > SUMIF >

Range: EmployeeTbl[Job Status]
Criteria: A4
Sum_range: EmployeeTbl[Current Salary]> OK

Select Total Salary column>Home > Number > General (click down arrow) >Accounting > Click Decrease Decimal twice

In cells D3 and D4, enter the AVERAGEIF function to calculate the average salary of part-time employees and the average salary of full-time employees, respectively. Format the Average Salary column with the Accounting format and no decimal places.