Learn how to use text and array formulas to locate potential payroll problems.

Required

a. Download the spreadsheet for this problem from the course Web site.

b. On the timecards data worksheet, add a column titled “Ghost Employee.” Write a function for that column that compares the employee# in the timecards worksheet to the employee# in the payroll master file worksheet and displays the message: “This employee does not exist in the payroll master file” for any employee in the timecards worksheet who is not listed in the payroll master file worksheet. The function should leave the cell blank if the employee# in the timecards worksheet does exist in the payroll master file worksheet.

c. On the payroll master file worksheet, add a column titled “Invalid SSN.” Write a function for that column to identify invalid Social Security numbers. Assume that Social Security numbers that begin with the digit 9 or that have the digits 00 for the middle two numbers are invalid. Your function should display a message that flags either of these two conditions or which displays nothing otherwise.