 Details
 Parent Category: Programming Assignments' Solutions
We Helped With This Excel Programming Homework: Have A Similar One?
Category  Programming 

Subject  Excel 
Difficulty  Undergraduate 
Status  Solved 
More Info  Need Help With Excel 
Short Assignment Requirements
Assignment Description
MGTS 103: Lab Exercise 3. Pivot tables, Descriptive Statistics and 5number summary
1. Open the Lab3Demo file and Save As… (add your name to the file name).
2. Enter your name, ID number and today’s date into NAME worksheet
3. On the DESCRIPTIVE worksheet, use Data Analysis Tool “Descriptive Statistics” to output useful summary statistics about Stantec and the S&P/TSX numbers.
4. Go to the PIVOT worksheet. Make a Pivot table showing a crosstabulation of consumer responses by which Region of the country they live in and which Product they purchased:
 Columns: Product 
Rows: Region  Data: Recommend

a. Use the data from Recommend for the data area of the pivot table, and be sure to select COUNT of, not SUM of, nor AVERAGE of, etc. Direct the output from the Pivot Table Wizard to cell $B$3 of the existing PIVOT TABLE worksheet.
b. Make 4 copies the pivot table output (and title) and Paste Special > Values on to the same worksheet, leaving a few blank rows between the two tables. Using the first copy of the pivot table to reference cell data, compute the percent frequencies for each row and column in the table, considering the grand total (COUNT) as 100%. Add a title to this table.
c. Using the first copy of the pivot table to reference cell data, complete a Percent Frequency Table using the (i) regional totals as your grand total and another table using (ii) product brand totals as your grand total. Think about how these tables tie into probabilities given a certain fact.
d. Drilldown (by doubleclicking) on any cell of the original pivot table and observe that the underlying data for that cell of the table appear on a new worksheet in this workbook. Note that the table resulting from Copy>Paste Special>Values does not keep the link to the data. This is important to know if you must maintain confidentiality of the underlying data.
5. Modify the original pivot table to show the AVERAGE value of the Recommend variable. Add a title to this table.
6. Use formulas to produce 5number summary
a. In first column, enter the values 0,1,2,3,4; these will represent quartile labels with 0 = minimum and 4 = maximum values observed for variables.
b. In the adjacent column use the =QUARTILE() formula with mixed referencing so that you can propagate this formula to the four cells beneath it.
7. Refer to the Independent Lab file to answer the questions in Blackboard Assessments A3.