Excel Lookup Functions

Excel Lookup Functions

Updated 2016.10.04

Load the WKRP file. Then do the following. Points are in square brackets. 50 points.

This worksheet contains data in only four fields: Last name, first name, hourly rate of pay, and number of hours worked. You must enter formulas to calculate the following:

Formatting:

·  [1] Save the file with the name WKRP-VLookup-FirstName-Lastname.xlsx using your name.

·  [1] Set the font for the entire worksheet to 10-point Verdana.

·  [1] Set the height of each row in the worksheet to 15.00.

·  [1] Merge and center the text in cell A1 over columns A through L.

·  [1] Set the cell style for A1 to Title. If necessary, adjust the height of row 1.

·  [1] Format all column headings (A3:L3) using the Heading 3 cell style.

·  [1] Turn on the wrap text feature for the column headings. If necessary, adjust the height of row 3.

·  [1] Center the column headings (A3:L3).

Formulas

·  [3] Regular Hours: If the employee worked 40 or fewer hours, then the regular hours are the same as the hours worked. If the employee worked more than 40 hours, then the regular hours is 40.

·  [3] Overtime Hours: hours over 40, if any. If the employee worked 40 or fewer hours, then the overtime hours value is 0. If the employee worked more than 40 hours, then the overtime hours is the number of hours over 40.

·  [2] Overtime Rate: 1.5 times the hourly rate. This number should appear for all employees, even those who have no overtime hours.

·  [2] Gross Pay: regular hours times hourly rate, plus overtime hours times overtime rate.

·  [2] Federal Withholding: look up the federal withholding rate in the table on the Tables worksheet. Look up the gross pay in the first column and use it to find the federal withholding rate. Multiply the withholding rate by the gross pay to determine the federal withholding amount.

·  [2] State Withholding: look up the state withholding rate in the table on the Tables worksheet. Look up the gross pay in the first column and use it to find the state withholding rate. Multiply the state withholding rate by the gross pay to determine the state withholding amount.

·  [2] Social Security: multiply the gross pay by the Social Security withholding rate (on the Tables worksheet).

·  [2] Net Pay: subtract the federal withholding, the state withholding, and the social security withholding from the gross pay.

·  [2] Totals: Put totals at the bottom of columns H through L.

·  [1] Sort the data in alphabetical order using Last Name as the key.

Formatting

·  [1] Format all dollar values as Accounting with two decimal places.

·  [1] Format all hour values (columns D, E, and F) with a Comma and one decimal place.

·  [1] Make all columns wide enough to display all of the data in the column.

·  [1] Format the totals at the bottoms of columns H through L with the Totals cell style. Re-apply the $ format if necessary.

Chart

·  [1] Create a 2D clustered bar chart (not a column chart) using the employee last names and their net pay.

·  [1] Use the Move Chart button to put the chart on a worksheet by itself.

·  [1] Change the name of the chart worksheet (the tab at the bottom) to Payroll Chart.

·  [1] Change the color of the chart worksheet tab to Blue from the Standard Colors row.

·  [1] Set the Plot Area (the area behind the bars) to a Gradient Fill. Use the Preset Gradient called Top Spotlight, Accent 6.

·  [1] Select all of the bars. Apply the Shape Effect called Bevel, Circle.

·  [1] Remove any legend.

·  [1] Change the chart title to: WKRP Payroll

·  [1] Set the font size of the title to 20 points.

·  [1] Change the font size for both axes to 14.

·  [1] Format the numbers on the x-axis so that they have 0 decimal places.

·  [1] You sorted the names in alphabetical order but they are listed in reverse order on the chart. Figure out how to get the names back in ascending order without changing their order back on the worksheet. Note that this will also put the x-axis numbers across the top of the chart.

Preparation for Printing the Payroll worksheet (not the chart worksheet)

·  [1] Add your name to the right part of the header (data page, not the chart page).

·  [1] Set the page orientation to Landscape.

·  [1] Make sure that your worksheet is scaled so that it fits on a single page.

·  [1] Center the worksheet vertically on the page.