Autotutorial 3
Microsoft Excel – Advanced Formulas
20 Points
NOTE: AUTOTUTORIALS ARE WORTH MORE THAN HOMEWORKS!!
A.General Instructions
a. To begin the assignment, download and open the file AT3_SQ25.xlsx. Your completed assignment should be named “Section_LastName_FirstName_AT3.xlsx”
b. 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?”.
c. Above the columns for Quarterly Sales is a Commission & Bonus table. You will use this table to complete one of the unfinished columns.
d. 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.
e. You will also be creating a chart to summarize the data and formatting the spreadsheet to be more legible.
B. Completing Formulas
a. In the “Total Sales” column, calculate the total sales for each employee’s first through fourth quarter sales. In cell F12, enter the correct formula and fill down the formula through F24.
b. In cell F25, calculate the Average Total Sales using the Average Function.
c. In the “Commission & Bonus” column (H), use the Lookup function*. Apply the correct formula to cell H12 (based on total sales) and fill down the formula through H24. (Hint: At one point, you will need to use absolute referencing on certain ranges so that your formula works for the entire column.)
d. In the “Total Salary” column (I), you will need to sum together the employee’s “Salary” and “Commission & Bonus.”
e. In the “Years w/ Firm” column (K), you will need to use the Now() function* to calculate how many years each employee has been with the firm (Hint: Difference between Now() and ‘Date hired’ will give the duration in days which you will need to divide by # of days in a year).
f. In the “Training Required?” column (L), you will need to use the Iffunction* to indicate who requires training based on their Total Sales. Employees with sales greater or equal to $100 do not require training (No), whereas employees with sales less than $100 require training (Yes). *See Section E for more information
C. Chart
a. Create a Stacked Column Chart using the employees’ names and their first through fourth quarter sales.
b. Title the chart “PLS21 SQ25 Quarterly Sales” and center align.
c. The first through fourth 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. Make sure both are on your chart.
d. Place your chart under your data.
D.Formatting
a. 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 32 font, and centered between A1:M1 (you can decide what method to use to center it). Make the row taller so you can read the text if necessary.
b. Move ranges B13:L28 to C13:M28 (All the cells should shift to the right). In the range B15:B27, insert a Sparkline to represent the first through fourth quarters of each employee (the range of cells C15:F27).
c. In the range C15:F27, insert Conditional formatting such as Data Bars, Color Scales, or Icon Sets (This will show up as well as the number). You only have to choose one.
d. On the bottom of row 2, insert a thick bottom border for the range A2:M2. After that there should be a blank row (row 3) in between your border and the PLS 21 Associate Sales Employees Report Title.
e. Apply all borders to the range A13:M27 (Don’t include diagonal borders across every cell). Apply a thick box border around the outside of the range C28:G28.
f. Apply dollar signs to the range C15:J27.
g. Next to where it says “Comments” on row 30, place thin bottom borders in between ranges B30:G30 and B31:G31.
h. Format your spreadsheet to have landscape orientation and set it to fit to: 1 page wide by 2 pages tall. (Your data should end up on the first page and your chart on the second). Ensure that both table and chart are centered vertically and horizontally in the pages.
i. Locking cells:
i. Finally, after all of this hard work you don’t want anyone inappropriately changing the information. Check to make sure that the range A13:M28 is locked. (Hint: Go to Format Cells).
ii. 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
a. Here are some details and descriptions ofthe functions you will use in Section B.
b. Feel free to YouTube any of these functions for further help.
LOOKUP Function
1. 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.
2. 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)
3. 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?
4. 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.
5. Once again, a plausible number will appear in cell H12 (H12 becomes I15 after inserting formatting rows).
NOW() function
1. NOW() is a date/time function that returns the number of days and fraction since 0:00:00 Jan. 1, 1900.
2. 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.
3. 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()
4. Use the NOW() function to calculate how long it has been since something started by subtracting the starting date from NOW().
5. Remember that NOW() returns days, so if you want to calculate the number of years you must apply the factor necessary to go from days to years (does not require another function).
6. You should have a sensible answer in cell K12 (K12 becomes L15 after inserting formatting rows).
IF (Logical Expression, Result if TRUE, Result if FALSE) Function
1. This is a logical function that returns one value if the given logical expression is true and another message if the expression is false
2. 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.
3. In the dialogue box, all employees with sales less than $100 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).
4. When finished, click on ENTER and you should have a logical answer in cell L12 (L12 becomes M15 after inserting formatting rows).