A.General Instructions

1. To begin the assignment, please open the file AT3.xlsx available on Canvas.
2. Once the file is open, you will see a spreadsheet with five unfinished columns: “Total Sales”, “Commission & Bonus”, “Total Salary”, “Years w/ Firm”, and “Training Required?”.
3. Above the columns for Quarterly Sales is a Bonus and Commission table. You will use this table to complete one of the unfinished columns.
4. Use Excel Help and Section E of these instructions to read about the Excel Lookup,Now(), and If functions. After reading the descriptions for each function, you will be able to apply them to the correct unfinished columns.
5. You will also be creating a chart to summarize the data and formatting the spreadsheet to be more legible.

B.Completing Formulas

*See Section E

for more info

1.

2. 3.

4. 5. 6.

In the “Total Sales” column, you will need to figure out the total sales for each employee’s first through fourth quarter sales. In cell F14, enter
the correct formula and fill down the formula through F26.
In cell F27, calculate the Average Total Sales using the Average Function. In the “Commission & Bonus” column, you will need to use the Lookup

function*. Apply the correct formula to cell H14 (based on total sales) and fill down the formula through H26. (Hint: At one point, you will need to use absolute referencing on certain ranges so that your formula works for the entire column.)

In the “Total Salary” column, you will need to sum together the employee’s “Salary” and “Commission & Bonus.”
In the “Years w/ Firm” column, you will need to use the Now() function* to calculate how many years each employee has been with the firm.
In the “Training Required?” column, you will also need to use the If function* to indicate who requires training based on their Total Sales. Employees with sales less than $75 do require training (Yes), whereas employees with sales more than $75 do not require training (No).

C.Chart

1. Create a Line Chart with Markers using the employees’ names and their first throughfourth quarter sales.
2. Title it “Quarterly Sales PLS21 Employees”.
3. The quarters should automatically appear in the legend, and the employee’s names should automatically appear along the x-axis if you highlight the chart’s columns correctly.
4. Place your chart under your data.

D.Formatting

1. Insert three rows above the first line that’s titled “PLS 21 Associates Sales Employees Report”. On the first of the three rows you inserted, type “U.C. Davis”. Make the title bold, size 20 font, and centered on the page(you can decide what method to use to center it).
2. Move ranges B15:L30 to C15:M30 (All the cells should shift to the right). In the range B17:B29, insert a Sparkline for the range of cells C17:F29.
3. In the range C17:F29, insert Conditional formatting such as Data Bars, Color Scales, or Icon Sets (This will show up as well as the number). You only have tochoose one.
4. On the bottom of row 2, insert a thick bottom border for the range A2:L2. Afterthat there should be a blank row in between your border and the PLS 21 Title.
5. Apply all borders to the range A15:M29 (Don’t include diagonal borders acrossevery cell). Apply a thick black border around the outside of the range C30:G30.
6. Apply dollar signs to the range C17:J29.
7. Next to where it says “Comments” on row 32, place bottom borders on ranges B32:G32 and B33:G33.
8. Format your spreadsheet to have landscape orientation and set it to fit to:

9.

1 page wide by 2 pages tall. (Your data should end up on the first page and your chart onthe second).

Locking cells:

1. Finally, after all of this hard work you don’t want anyone inappropriately changing the information. Check to make sure that the range A15:M30 is locked. (Hint: Go to Format Cells).
2. After the range is locked, protect the sheets so users cannot modify your spreadsheet. Do not check the boxes for “select locked cells” and “select unlocked cells”.

iii. Do NOT add a password to your sheet.

E. Function Help

2. NOW() function

i. ii.

1.

Here are some details and descriptions of the functions you are to use in Section B.
Feel free to YouTube any of these functions for further help.

LOOKUP Function

LOOKUP is a function that fetches a value from a lookup table on the worksheet. This lookup table can consist of two vectors: a lookup vector and a result vector. A vector is a range of cells and is written as two cell addresses separated by a colon. For example, the cells between and including A3 and A7 are written as A3:A7.

• — To use the LOOKUP function, click on the FX button next to the formula bar. In the “Insert Function” or “Formula Builder” dialogue box, type LOOKUP in the search box to find the LOOKUP function. Then, click OK. At this point, you will be asked to choose one of two formulas. Choose this formula: =LOOKUP(lookup_value, lookup_vector, result_vector)
• — Now, you will be asked to enter the appropriate information for each line. The lookup_value line requires only one cell. The lookup_vector requires a group of cells. Looking at the Bonus Table provided, which column do you think might be the lookup value and the lookup_vector? Finally, the result_vector requires a group of cells. Which group do you think this might be?
• — At the bottom of the dialogue box you will see Formula Result = a reasonable number. You can check calculations and formulas by hand. Now click OK.
• — Once again, a plausible number will appear in cell H14 (H14 becomes I17 after inserting formatting rows). The answer for Bella’s Commission & Bonus should be $5.00.

NOW() is a date/time function that returns the number of days and fraction

since 0:00:00 Jan. 1, 1900.

• — To use the NOW() function, click on the FX button next to the formula bar. In the “Insert Function” or “Formula Builder” dialogue box, type NOW() in the search box to find the NOW() function. Then, click OK.
• — NOW() is a code for today’s date, so if you wanted a cell to include today’s date, all you would do is type in: =NOW()
• — Use the NOW() function to calculate how long it has been since something started by subtracting the starting date from NOW().
• — Remember that NOW() returns days, so if you want to calculate

number of years you must apply the factor necessary to go from

days to years.
— You should have a sensible answer in cell K14 (K14 becomes L17

after inserting formatting rows).

3. IF (Logical Expression, Result if TRUE, Result if FALSE) Function

This is a logical function that returns one value if the given logical expression is true and another message if the expression is false

• — To use the IF function, click on the FX button next to the formula bar. In the “Insert Function” or “Formula Builder” dialogue box, type IF in the search box to find the IF function. Then, click OK.
• — In the dialogue box, all employees with sales less than $75 do require training. What do you think the logical test might be? In the second box, you need to type in a response if the logical test is true, and the third line should have a response if the logical test is false. What do you think the appropriate responses would be? (Hint: No/Yes).
• — When finished, click on ENTER and you should have a logical answer in cell L14 (L14 becomes M17 after inserting formatting rows).