The campus JuiceLand manager recorded all smoothie sales during one business day as exploratory analysis for a strategic effort to understand the customer gender distribution as well as the time of day when most smoothies are sold. Use the “JuiceLand” worksheet that you moved into your PS2 workbook. i. Create Named Ranges for Columns C, D, and E; assign the names “Price”, “Gender”, “Shift” (respectively) to the data in rows 4 through 61 in each column.

ii. Use Excel Functions (hint: related to IF) to fill in the summary tables with calculations: Count of Sales, Total Sales, and Average Sales. Use Mixed Referencessuch that the formulas that you enter in cells H4, H11, and H18may be dragged one column to the right and two rows down (filling the green highlighted cells in each section). Use the Named Ranges that you created (“Price”, “Gender”, and “Shift”)in your formulas instead of absolute references. Reference table label cells in your formulas rather than hardcoding the text categories. For example: in cell H4, use a reference to cell H3 instead of typing “Male” and use a reference to cell G4 rather than typing “Morning”.

iii. Use Excel Functionstofill in the right-most column and bottom row around the green cells marking the interior of each summary table. Don’t forget the Grand Totals/ Grand Averagein cells J7, J14, J21.
- Cells displaying sales amounts should have appropriate formatting for financial values (£2 decimal places) and some indication of units. Cells that do not contain financial values should display no decimal places.

4)JuiceLandPivotUse the “JuiceLand” worksheet. Insert a PivotTable for data in A3: E61, placing the report on a new worksheet. Re-name that new worksheet “JuicePivot” or something similar.
Re-create the same three summary tables (Count of Sales, Total Sales, and Average Sales) as in Question #3(but this time you can use the drag & drop PivotTables rather than functions).
Change the Pivot Report Layoutto Outline or Tabular form. As with #3, cells displaying sales amounts should have appropriate formatting for financial values and some indication of units. Cells that do not contain financial values should display no decimal places.

5) Cain and Abel’s

You are hired by a local bar to design an Excel worksheet that could be used to determine whether patrons are old enough to enter. All customers must be at least 21 years of age –no exceptions. Create a well-labeled worksheet using two cells (2)for calculations:

i. One cell for an employee at the door to enter a patron’s birthdate as listed on their state-issued ID. Use a Short Date or Long Date format.

ii. One cell to return a decision if the patron is old enough to enter. Use a formula to automatically display “Welcome” if at least 21 years old or “Underage” otherwise.

- You will likely want to incorporate the TODAY function and IF function (or another Logical or Time/Date function of your choice). Account for Leap Years by using 365.2 days per year in your calculations.

(2) It’s fine to write labels in additional cells to indicate how the two active cells are used (for example, Enter Birthdate: ). But your calculation should appear in one cell only using only a formula and the birthdate cell as an input reference.