Build a pivot table which calculates the average andmaximum sales by department
You will have to combine the data using VLOOKUP or MATCH/INDEX.Note any observations you see regarding trends in the results.
Use the data below to solve the problem. List steps anddirections step by step with any formulas, what has been clickedon, and any other important information to solve the problem usingExcel. Thank you.
| Sales Data |
| Loyalty ID # | Sale Total | Department |
| 1391 | $ 514.72 | Clothing |
| 1804 | $ 109.85 | Housing |
| 1473 | $ 328.42 | Housing |
| 1847 | $ 124.78 | Athletic |
| 1586 | $ 79.84 | Other |
| 1243 | $ 147.32 | Luggage |
| 1117 | $ 235.15 | Clothing |
| 1995 | $ 89.20 | Hardware |
| 1591 | $ 38.53 | Luggage |
| 1919 | $ 53.63 | Hardware |
| 1957 | $ 273.65 | Other |
| 1397 | $ 58.50 | Athletic |
| 1118 | $ 184.85 | Luggage |
| 1354 | $ 104.02 | Athletic |
| 1414 | $ 36.25 | Other |
| 1714 | $ 263.19 | Clothing |
| 1826 | $ 75.42 | Athletic |
| 1591 | $ 200.31 | Hardware |
| 1387 | $ 71.77 | Other |
| 1147 | $ 225.76 | Other |
| 1298 | $ 168.35 | Athletic |
| 1045 | $ 41.81 | Clothing |
| 1022 | $ 66.31 | Other |
| 1290 | $ 12.87 | Other |
| 1059 | $ 309.17 | Athletic |
| 1522 | $ 64.22 | Other |
| 1533 | $ 210.60 | Hardware |
| 1266 | $ 286.38 | Clothing |
