excel

Using the Final-Exam-Excel-Your name.xlsm

Copy the information of the Tab “Q2-A” to the Tab “Q3” and calculate:

· The means, median, mode, and standard deviation of Sales and COGS for Year 1 in columns C and D at the end of the data.

· Calculate the 3rd largest number of Sales and COGS. (Use the LARGE function)

Add labels and format them correctly.

Example:

Means = 195,48 6.72 51,29 9.51

Median = 180,86 5.00 47,668.88

Mode = 162,35 9.00 51,36 1.75

Standard Deviation = 54,95 5.01 15,92 0.79

3rd largest number = 265,36 6.00 74,80 0.50

Using the Final-Exam-Excel-Yourname.xlsm

Copy the information of the Tab “Q2-C” to the Tab “Q5” and calculate:

A 3-D Column Chart of COGS of Year 3 by Store Name. Change the title to “Sales by Year 3”.

Verify that all the information and labels are correct. Add your name

Using the Final-Exam-Excel-Yourname.xlsm

Insert a Pivot Table in the Tab for Q9 that shows:

The Sum of Units Sold  by  Planner Code . (Name the Tab Q9-1)  

What is the total Qty Sold in Quarter 3?

(Name the Tab Q9-2)  

Hint: you must group by quarter.

Using the Final-Exam-Excel-Yourname.xlsm

In tab Q10 Record a Macro to record the following actions:

1- Call the Macro: Final EXCEL question 10

2- Select the range B5:G174

3- In the Home menu select “Format as Table” and select the “ Blue ” format.

4-Select the records with Department = “ 060 ROASTED COFFEE ”

5- In Cell E3 type your name.