Before diving into this assignment, I very strongly recommend that you watch the videos on Panopto.

The workbook associated with this homework contains two worksheets:

• An ‘Answers’ sheet that you fill in with the answers and explanations for the questions below in this Word
• A ‘Pizza Orders’ sheet that contains a spreadsheet of all pizza orders made at Pop’s Pizza in

This sheet has 6 columns:

• The Date of an order
• The Pizza Product that was ordered
• 3 toppings columns
• A Subtotal Column

You are going to be asked to use some of the filtering and data analytics functions you learned in class to authoritatively answer the questions below. As you are becoming more accustomed to Excel, I won’t be holding your hand as much, and you will be free to lay out your resulting work spreadsheets in any manner you see fit so long as they clearly answer the questions.

Please note: I will be grading your ability to not only answer the questions I ask, but also your ability to convey and show your work in a clear, easy to understand manner. If you provide me answers for a problem but don’t have a worksheet where I can go and find the work you used to get your answers, you will receive no credit for that problem. If your work is not laid out in a way that the average layperson could look at the output and understand what’s being conveyed, you will lose 25% credit for the problem. Part of learning how to use Excel is learning to make elegant spreadsheets. I want you to put thought into the appearance of your work, knowing that you will often need to share your Excel workbooks with others.

Each question will be graded as follows:

• 25% – Is the question answered correctly? [unless there is no backing work at all, 0% will be awarded to the whole problem)
• 25% – Does the explanation/justification accurately describe the answer
• 25% – Is the backing work using a proper filter/formula / function
• 25% – Is the backing work laid out in a manner that’s easy for the average layperson to look at and

understand?

Note: I’m happy to give partial credit. If you’ve come to the wrong answer, but have work that shows me how you got to that answer, you will receive partial credit for the problem.

This should be your easiest problem, to ensure you get full credit on at least one problem and to lower the overall value of all other problems.

Please make a copy of your ‘Pizza Orders’ sheet and call it ‘Pizza Orders – Copy’. As you move forward with the remaining problems, you can make any further sheet copies or data manipulation against the ‘Pizza Orders – Copy’ sheet, leaving yourself with a pristine original ‘Pizza Orders’ sheet as a fallback.

Problem 2
NOTE: If you create any worksheets in order to answer this problem, please name all worksheets created such that they start with “P2-” (EX: P2-AdvFiltering)
Using advanced filtering, please create a set of results that displays all Small Pizzas that contain Pepperoni OR Onions. After creating the advanced filter, use the simple math functions [these are the functions that don’t contain …IF or …IFS] to find the Count of records in your result set, as well as the Sum of the subtotal of your results.
When you are finished, go to your ‘Answers’ sheet and tell me what your Count of records and your Sum of the Subtotal was. Use the ‘Explanation / Justification’ column to provide explanation of your answers, letting me know which worksheet I can go to in order to see your work.

NOTE: If you create any worksheets in order to answer this problem, please name all worksheets created such that they start with “P8-” (EX: P8-Work)
Using the Database functions you learned (DCOUNT, DCOUNTA, DSUM, DAVERAGE), please provide me with the following information:
• The Count of all Small pizzas sold in June that contained Mushrooms
• The Count of all Medium pizzas sold in June that contained Mushrooms
• The Count of all Large pizzas sold in June that contained Mushrooms
• The Count of all X-Large pizzas sold in June that contained Mushrooms
• The Sum of the subtotal column for Small pizzas sold in June that contained Mushrooms
• The Sum of the subtotal column for Medium pizzas sold in June that contained Mushrooms
• The Sum of the subtotal column for Large pizzas sold in June that contained Mushrooms
• The Sum of the subtotal column for X-Large pizzas sold in June that contained Mushrooms
• The Average of the Subtotal column for all Small pizzas sold in June that contained Mushrooms
• The Average of the Subtotal column for all Medium pizzas sold in June that contained Mushrooms
• The Average of the Subtotal column for all Large pizzas sold in June that contained Mushrooms
• The Average of the Subtotal column for all X-Large pizzas sold in June that contained Mushrooms
When you are finished, go to your ‘Answers’ sheet and tell me what your Count, Sum, and Average were. Use the ‘Explanation / Justification’ column to provide explanation of your answers, letting me know which worksheet I can go to in order to see your work.
Note: This is an especially tricky problem to solve elegantly. Make sure to watch the Panopto videos for a way to solve it!