Excel Problem Objective: 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. In column I, under the label “Ghost Employee?” write a function that compares the employee# in the time cards column to the employee# in the payroll master data column and displays the message: “Time card employee# does not exist in master data”

for any employee in the time cards column who is not listed in the payroll master data column. The function should leave the cell blank if the employee# in the time cards worksheet does exist in the payroll master file worksheet. (Hint: Use the ISNA and MATCH functions.)

c. In column L, titled “Invalid SSN?” write a function 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 that displays nothing otherwise. (Hint: There are text functions that examine specific portions of a string, such as the left three characters, and there are also functions that convert text to numeric values.)

d. In column P, titled “Missing Paycheck?” write a function to check whether a time card exists for each employee in the master payroll data section of the worksheet.

The formula should either return the message “No paycheck created for this employee” or display nothing.