Answered step by step
Verified Expert Solution
Question
1 Approved Answer
the whole spreadsheet is posted above Excel's PivotTable Report provides an excellent way to summarize data for two or more variables simultaneously. The goal of
the whole spreadsheet is posted above
Excel's PivotTable Report provides an excellent way to summarize data for two or more variables simultaneously. The goal of this Excel Graded Tutorial is to familiarize you with Excel's PivotTable feature and learn about its uses. You will learn how to use PivotTables by developing a crosstabulation of region, quality rating, and meal price for 300 restaurants and then answering questions about the data. The data for this problem is located on the 'Data' sheet in columns A through D of the Excel Online file below. An empty PivotTable is provided on the 'Pivot' sheet. Click on the 'Pivot' tab to select the Pivottable sheet. Inside the PivotTable1 area right-click and select Show Field List. The Pivottable task pane should now be displayed on the right side of the spreadsheet. In the field list you should see Restaurant, Region, Quality Rating, and Meal Price ($). Construct a spreadsheet to answer the following questions. X Open spreadsheet Questions 1. What are the sales totals for each region? To answer this question, proceed as follows. First, click on Region in the Field List and then drag it to the rows box in the PivotTable pane. You will see the four regions enter the PivotTable. Next, drag Meal Price ($) into the values box of the PivotTable pane. The second column in the Pivot Table should now be Sum of Meal Price ($). What are the total sales for each Region and the Grand Total? Total Sales East $ X North $ 106 South $ West $ Grand Total $ 2. What are the sales totals for each Quality Rating? To answer this question, proceed as follows. First, click on Region in the rows box of the PivotTable (where it was used to answer Question 1) and then drag it back into the Field List. Next, drag Quality Rating from the Field List into the rows box of the PivotTable pane. What are the total sales for each Quality Rating? Total Sales Excellent $ Good $ Very Good $ 3. What is the sales total for a particular combination of Region and Quality Rating? For example, what is the sales total for Excellent restaurants in the West? To answer this question, proceed as follows. Drag Quality rating from the rows box to the column box in the PivotTable pane. Then drag Region from the Field List into the rows box. The intersection of West and Excellent in the PivotTable provides the answer. Total sales for Excellent meals in the West Region. Total sales for Excellent restaurants in the West are $ 4. What is the average meal price for the entire East region and Very Good meals in the East region to the nearest cent? To answer this question, proceed as follows. Select all numeric values in the PivotTable as is appears from Question 3 above Next no to the values boy in the DivotTable nane and click on Sum of Meal Prices and select Value Field Settings in Value Field Settings selert Average then click OK In the Values box it should now Questions 1. What are the sales totals for each region? To answer this question, proceed as follows. First, click on Region in the Field List and then drag it to the rows box in the PivotTable pane. You will see the four regions enter the Pivottable. Next, drag Meal Price ($) into the values box of the PivotTable pane. The second column in the Pivot Table should now be Sum of Meal Price ($). What are the total sales for each Region and the Grand Total? Total Sales East $ North $ 106 South $ West $ Grand Total $ 2. What are the sales totals for each Quality Rating? To answer this question, proceed as follows. First, click on Region in the rows box of the PivotTable (where it was used to answer Question 1) and then drag it back into the Field List. Next, drag Quality Rating from the Field List into the rows box of the PivotTable pane. What are the total sales for each Quality Rating? Total Sales Excellent $ Good $ Very Good $ 3. What is the sales total for a particular combination of Region and Quality Rating? For example, what is the sales total for Excellent restaurants in the West? To answer this question, proceed as follows. Drag Quality rating from the rows box to the column box in the PivotTable pane. Then drag Region from the Field List into the rows box. The intersection of West and Excellent in the PivotTable provides the answer. Total sales for Excellent meals in the West Region. Total sales for Excellent restaurants in the West are $ 4. What is the average meal price for the entire East region and Very Good meals in the East region to the nearest cent? To answer this question, proceed as follows. Select all numeric values in the PivotTable as is appears from Question 3 above. Next, go to the Values box in the PivotTable pane and click on Sum of Meal Prices and select Value Field Settings. In Value Field Settings select Average. Then click OK. In the Values box, it should now read Average of Meal Price ($). Average meal price in the East Region is $ (to the nearest cent). Average meal price for Very Good meals in the East Region is $ (to the nearest cent). Check My Work Reset Problem Question 1 Total Sales East North South West Grand Total Question 2 Total Sales Excellent Good Very Good Question 3 Total Sales (Excellent, West) Question 4 4 1 Restaurant Region Quality Rating Meal Price ($ 2 2 1 South Good 44 3 3. 2 East Excellent 24 4 3 North Excellent 32 5 4 East Good 40 6 5 South Good 31 7 6 North Very Good 28 8 7 North Good 32 9 9 8 East Very Good 13 10 9 West Excellent 22 11 10 West Excellent 12 12 11 West Good 47 13 12 North Good 14 14 13 North Good 36 15 14 East Very Good 12 16 15 East Excellent 38 17 16 West Excellent 37 18 17 East Good 52 19 18 West VeryGood 25 20 19 North Excellent 40 21 20 North Very Good 44 22 21 South Excellent 19 23 22 East Good 34 24 23 South Excellent 14 25 24 East Good 44 26 25 East Good 55 27 26 North Good 43 28 27 South Good 37 29 28 South Excellent 48 30 29 North Very Good 26 31 30 East Good 31 32 31 West Excellent 45 33 32 East Good 31 34 33 South Excellent 16 35 34 North Good 46 36 35 West Very Good 42 37 36 West Excellent 34 38 37 East Good 45 39 38 East Excellent 22 40 39 North Good 42 41 40 East Good 11 42 41 West Excellent 40 Average Meal Price (East) Average Meal Price (Very Good, East) Restaurant E F G H 1 K L 3. N TL 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 02 Region Quality Rating Meal Price ($) 40 East Good 41 West Excellent 42 West Excellent 43 East Excellent 44 West Good 45 North Very Good 46 North Good 47 East VeryGood 48 North Very Good 49 East Very Good 50 South Good 51 East Good 52 West Good 53 West Very Good 54 East Excellent 55 West Very Good 56 North Excellent 57 South Excellent 58 North Excellent 59 South Good 60 North Excellent 61 South Good 62 West Good 63 North VeryGood 64 North Excellent 65 South Excellent 66 East Very Good 67 South Excellent 68 West Very Good 69 North VeryGood 70 South Excellent 71 South Excellent 72 East Good 73 East Good 74 East Good 75 North VeryGood 76 South Very Good 77 East 78 West Good 79 East Very Good 80 West Very Good 81 Mact Furallant 11 40 28 31 23 26 52 15 45 28 38 52 17 46 30 46 49 46 16 26 34 24 38 19 54 53 12 20 30 30 19 21 21 51 43 20 34 41 27 24 46 18 GoodStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started