**Excel – Tutorial 8**

**Working with Advanced Functions (Application Assignment)**

** **

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
workbook downloaded from Blackboard, and save the workbook as*Employees*in your CS245 folder you created.*MBHC Employees*

- 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),**Yes**should 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 messageif an error value occurs. Test the modified formula by changing the date in cell E2 from 7/10/2014 to*Invalid hire date***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 messageif an error value occurs. AutoFit the column.*Invalid hire date*

**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.**

** **

**Select Cell D3 > Formulas > Function Library > More Function (click down arrow) > Statistical (click down arrow) >AVERAGEIF >**

** **

**Range:**__EmployeeTbl[Job Status]__**Criteria:**__A3__**Average_range:**__EmployeeTbl[Current Salary]__> OK

** **

**Select Cell D4 > Formulas > Function Library > More Function (click down arrow) > Statistical (click down arrow) >AVERAGEIF >**

** **

**Range:**__EmployeeTbl[Job Status]__**Criteria:**__A4__**Average_range:**__EmployeeTbl[Current Salary]__> OK

** **

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

- Save the workbook, and then close it. Upload unto Blackboard to receive credit.