This for an exercise. I am using Excel 2021 for Mac.
The dataset we're given is labeled by hours 1-24 in rows and individual dates (from 1/1/20 to 12/31) in columns. It lists out all of the prices by date and by hour for "Jim's Store" and then does the same thing for "Jane's Store" directly right below Jim's. The exercise is asking me to calculate, for all months in 2020 and for both stores, (1) the avg. price across all hours (2) avg. price across only peak hours and (3) avg. price across Non-Peak hours.
I don't really know the given sample calculations are meant to push my approach in one way or another, but I approached it a bit different and I wanted someone to let me know if I'm going about this wrong. I'm absolutely an Excel novice and have less than 2-3 days of Excel knowledge.
Here are the steps I took.
1. Selected the store, date, and all of the hours column as well as all of the data below to then insert a PivotTable based on that data in a new sheet.
2. Please reference the attached picture for how I set up my Pivot Table but essentially I moved "Stores" into "Rows", "Months" into "Columns", and "Date" into "Filters". I moved each of the 24 Hour fields into "Values" and changed the field setting to calculate the average for each hour. I also put the "Values" field into the "Rows" section of the PivotTable tool on the right.
So now I have the average price for each hour for each month for both Jim and Jane, but something tells me there's a much easier and cleaner way to display this data. But if I got this far and made it look like this, I have no idea what I would need to do for to calculate the peak and non peak hours for each store. My thinking is that if I can get this pivot table to look a bit more cleaner and well displayed, why not just make 2 more pivot tables with the same dataset, and use the "filter" function of a pivot table to exclude the specific parameters that define peak vs non-peak hours.
To be honest, this exercise is part of a job interview process, and I was told that there really isn't a right answer per se and they just want to gauge how I would approach a problem like this. I was pretty transparent with them about not having any actual excel experience, so I guess they're use these exercise to see if I'm fit for another interview!
Anyways, I'll keep researching, but gosh does it suck to have to do this exercise from a MacBook! I really appreciate any help or suggestions provided regarding this problem, and thank you for reading!
The dataset we're given is labeled by hours 1-24 in rows and individual dates (from 1/1/20 to 12/31) in columns. It lists out all of the prices by date and by hour for "Jim's Store" and then does the same thing for "Jane's Store" directly right below Jim's. The exercise is asking me to calculate, for all months in 2020 and for both stores, (1) the avg. price across all hours (2) avg. price across only peak hours and (3) avg. price across Non-Peak hours.
- Peak hours:
- hours 8-23 on Monday - Friday, not including Store Holidays
- Non-peak hours:
- hours 1-7 & hour 24 on days Monday-Friday, not including Store Holidays
- hours 1-24 on days Saturday - Sunday
- hours 1-24 on Store Holidays
- Store holidays are 1/1, 5/25, 7/4, 9/7, 11/26, 12/25
| Part 2 (blank).xlsx | |||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
| 1 | Hourly_Online_Prices | ||||||||||||||||||||||||||||
| 2 | |||||||||||||||||||||||||||||
| 3 | Manual Examples | Shoe_Price | |||||||||||||||||||||||||||
| 4 | Jim's Avg. Jan '20 Total | $34.9926 | |||||||||||||||||||||||||||
| 5 | Jim's Avg. Jan '20 Peak | $35.0041 | |||||||||||||||||||||||||||
| 6 | Jim's Avg. Jan '20 Non-Peak | $34.9823 | |||||||||||||||||||||||||||
| 7 | |||||||||||||||||||||||||||||
| 8 | Hour | ||||||||||||||||||||||||||||
| 9 | Store | Date | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | |||
| 10 | Jim's | 1/1/20 | $31.6992 | $30.5115 | $37.6342 | $30.2575 | $32.2213 | $30.4321 | $37.4319 | $32.2977 | $32.5305 | $30.3367 | $31.7915 | $37.9923 | $39.8293 | $32.9674 | $36.6194 | $34.9222 | $31.6517 | $34.7415 | $38.1258 | $34.8405 | $39.6615 | $34.9142 | $36.3306 | $31.5972 | |||
| 11 | Jim's | 1/2/20 | $38.4143 | $30.4804 | $36.1181 | $31.3793 | $36.5937 | $32.9125 | $30.1631 | $37.8865 | $38.8188 | $32.6860 | $39.4400 | $33.2197 | $33.3511 | $37.8378 | $35.1233 | $35.8917 | $39.3743 | $32.2757 | $39.1379 | $37.1487 | $38.0503 | $32.1134 | $35.6078 | $36.6932 | |||
| 12 | Jim's | 1/3/20 | $34.6810 | $32.3218 | $38.7249 | $39.5939 | $34.4275 | $30.4154 | $35.6202 | $39.1408 | $37.6058 | $32.5984 | $33.5998 | $34.1623 | $38.5912 | $34.1979 | $33.8471 | $35.6418 | $34.3031 | $30.8876 | $34.3648 | $35.3240 | $34.8646 | $31.7107 | $37.5624 | $37.5842 | |||
| 13 | Jim's | 1/4/20 | $33.4997 | $37.4538 | $36.9632 | $36.1518 | $38.0904 | $39.1718 | $35.1454 | $33.5299 | $38.3818 | $39.2794 | $36.0251 | $39.8796 | $32.5936 | $38.1273 | $39.2340 | $32.4319 | $31.4229 | $39.2753 | $38.3822 | $34.8064 | $39.0010 | $32.3328 | $36.9969 | $38.2818 | |||
| 14 | Jim's | 1/5/20 | $31.8911 | $36.5902 | $39.7965 | $31.9386 | $31.9592 | $38.7669 | $30.7101 | $35.8923 | $37.0705 | $36.4278 | $38.7703 | $33.4863 | $36.9384 | $32.2052 | $37.0194 | $36.0485 | $34.9180 | $32.7707 | $30.3686 | $34.2880 | $38.0167 | $30.8594 | $31.7353 | $30.1511 | |||
| 15 | Jim's | 1/6/20 | $39.6321 | $36.3970 | $33.3301 | $36.3367 | $36.0135 | $34.0024 | $31.0391 | $31.3164 | $38.3520 | $39.9660 | $33.0835 | $39.6447 | $36.9602 | $36.7394 | $36.8171 | $36.5367 | $34.5397 | $32.9585 | $30.3666 | $35.6199 | $38.6363 | $35.9531 | $37.5356 | $34.6482 | |||
| 16 | Jim's | 1/7/20 | $30.9441 | $36.6012 | $35.1579 | $37.0998 | $39.8102 | $35.4471 | $33.9698 | $37.4246 | $37.0545 | $31.6508 | $31.1299 | $36.3928 | $39.2357 | $37.3216 | $33.5787 | $33.0311 | $30.8545 | $34.1993 | $36.5021 | $31.3960 | $34.7302 | $34.4599 | $39.5462 | $39.9506 | |||
| 17 | Jim's | 1/8/20 | $30.2550 | $37.2027 | $30.9093 | $32.2023 | $34.7766 | $32.3807 | $36.5859 | $38.8619 | $36.7414 | $39.3754 | $37.0191 | $35.5259 | $37.1628 | $30.1621 | $36.5398 | $31.5950 | $39.4987 | $34.7718 | $31.5591 | $31.4133 | $36.7066 | $33.8412 | $31.0117 | $39.0510 | |||
| 18 | Jim's | 1/9/20 | $35.1940 | $30.2706 | $34.2960 | $34.7000 | $34.9108 | $36.1878 | $33.8293 | $36.5490 | $30.5449 | $39.4583 | $36.4512 | $38.9405 | $35.0078 | $36.0901 | $37.5096 | $39.9233 | $31.7036 | $31.7574 | $36.9174 | $32.1609 | $33.8760 | $35.0759 | $31.6961 | $30.5801 | |||
| 19 | Jim's | 1/10/20 | $35.1712 | $32.5469 | $35.8720 | $30.1570 | $37.3355 | $33.5067 | $31.0455 | $34.7510 | $38.0613 | $32.0100 | $33.8740 | $35.8352 | $34.3982 | $37.2706 | $39.0307 | $31.3818 | $36.2900 | $32.7968 | $31.6210 | $33.5089 | $39.5312 | $39.7582 | $35.6323 | $31.1291 | |||
| 20 | Jim's | 1/11/20 | $30.8885 | $31.1582 | $37.3417 | $31.4458 | $34.0558 | $30.2328 | $35.0797 | $32.9854 | $34.8215 | $38.7125 | $35.7511 | $33.9059 | $35.3621 | $30.6190 | $32.2886 | $31.9127 | $39.6791 | $39.3425 | $32.4915 | $34.6775 | $39.7481 | $32.2312 | $39.0046 | $32.4352 | |||
| 21 | Jim's | 1/12/20 | $30.8749 | $39.8565 | $34.1625 | $32.9008 | $37.2781 | $38.0900 | $30.6453 | $34.7258 | $30.4367 | $36.9918 | $37.8623 | $37.2827 | $38.8990 | $38.4795 | $35.3499 | $31.4052 | $35.5999 | $39.2668 | $38.3773 | $34.3172 | $33.4599 | $38.0637 | $33.2259 | $37.3639 | |||
| 22 | Jim's | 1/13/20 | $32.1198 | $34.3067 | $37.3335 | $37.8196 | $32.3595 | $35.3295 | $36.8903 | $34.9359 | $36.4503 | $30.1096 | $36.7806 | $35.3961 | $33.5667 | $30.7394 | $34.7524 | $34.8019 | $38.2355 | $32.7207 | $34.1010 | $36.1220 | $39.5135 | $38.4986 | $30.0405 | $34.7313 | |||
| 23 | Jim's | 1/14/20 | $35.7059 | $35.7391 | $39.4771 | $34.2882 | $36.0272 | $30.9784 | $33.4564 | $39.7267 | $31.5260 | $30.1445 | $39.5350 | $32.1724 | $33.0437 | $37.5407 | $35.1092 | $30.9676 | $33.7286 | $37.5699 | $35.2861 | $32.2242 | $39.8692 | $38.4395 | $31.8270 | $32.4684 | |||
| 24 | Jim's | 1/15/20 | $39.7413 | $38.7583 | $39.9581 | $34.9525 | $33.3862 | $38.9137 | $38.3329 | $37.6555 | $38.7905 | $30.5685 | $32.9159 | $35.2530 | $37.9979 | $38.3185 | $38.5228 | $31.2079 | $36.8697 | $31.2415 | $38.6088 | $38.9540 | $34.2090 | $33.1110 | $32.9863 | $37.5224 | |||
| 25 | Jim's | 1/16/20 | $39.2765 | $32.6506 | $35.7568 | $37.5429 | $35.0073 | $31.0988 | $31.3753 | $39.9570 | $36.6088 | $34.7246 | $32.3559 | $34.7727 | $39.3256 | $36.1714 | $30.8868 | $32.8870 | $37.0554 | $32.8761 | $33.0328 | $31.5531 | $32.4809 | $34.5019 | $31.9877 | $37.7954 | |||
| 26 | Jim's | 1/17/20 | $33.0545 | $37.5610 | $34.6470 | $39.1434 | $32.3101 | $30.0985 | $31.5292 | $32.8523 | $31.9813 | $36.1980 | $35.2205 | $32.6771 | $30.2688 | $32.1939 | $30.2827 | $30.0826 | $31.1863 | $37.5880 | $35.7414 | $36.8505 | $37.4835 | $32.4986 | $31.5104 | $34.3838 | |||
| 27 | Jim's | 1/18/20 | $32.5617 | $32.3232 | $31.2809 | $32.8790 | $34.5984 | $34.0008 | $38.8108 | $36.7579 | $34.5721 | $38.0976 | $32.6992 | $34.6605 | $36.4709 | $30.2029 | $39.6902 | $35.4319 | $34.0747 | $39.9987 | $34.5385 | $36.0201 | $35.8123 | $36.8270 | $38.1677 | $31.6393 | |||
| 28 | Jim's | 1/19/20 | $35.0241 | $38.0819 | $31.1459 | $30.6892 | $30.1482 | $36.8757 | $38.8723 | $38.2254 | $33.8939 | $38.7271 | $30.1300 | $37.2981 | $34.3497 | $39.2239 | $33.6615 | $30.4814 | $30.8299 | $36.6752 | $30.0371 | $37.6724 | $35.7315 | $36.8236 | $36.6629 | $33.8316 | |||
| 29 | Jim's | 1/20/20 | $35.2671 | $32.8300 | $38.9120 | $37.6315 | $37.3160 | $31.2142 | $32.3652 | $32.1528 | $31.7419 | $30.2484 | $34.2535 | $34.6463 | $31.2184 | $33.8825 | $37.4652 | $35.1138 | $38.4493 | $34.1067 | $39.3273 | $39.7891 | $34.4477 | $32.8145 | $37.5814 | $39.1760 | |||
| 30 | Jim's | 1/21/20 | $32.6902 | $35.5686 | $37.8308 | $35.7951 | $35.4231 | $30.9552 | $36.2693 | $32.4966 | $31.0160 | $34.7534 | $35.1789 | $31.0649 | $31.4535 | $31.4460 | $37.0912 | $37.2035 | $38.6441 | $37.9717 | $35.6355 | $39.7255 | $32.5300 | $34.2917 | $35.3362 | $37.5592 | |||
| 31 | Jim's | 1/22/20 | $36.8852 | $39.6427 | $37.3983 | $37.0834 | $37.5246 | $30.7160 | $30.9011 | $31.2425 | $37.7535 | $38.8564 | $31.3829 | $30.6514 | $33.3093 | $34.2858 | $30.5604 | $38.2523 | $34.9153 | $38.7103 | $32.6308 | $36.0402 | $38.5748 | $31.1447 | $37.2711 | $34.1853 | |||
| 32 | Jim's | 1/23/20 | $35.0714 | $38.7584 | $33.7199 | $31.4013 | $32.5676 | $33.6348 | $38.0918 | $35.0071 | $32.8150 | $39.8443 | $30.9706 | $38.1429 | $33.8138 | $36.3138 | $38.5192 | $39.7733 | $36.6119 | $39.5472 | $32.8643 | $35.8164 | $38.7986 | $34.5131 | $33.4711 | $30.2761 | |||
| 33 | Jim's | 1/24/20 | $39.3762 | $31.8321 | $31.0878 | $31.2076 | $33.3827 | $36.4842 | $30.7741 | $30.4545 | $37.8513 | $32.0920 | $39.3039 | $37.4368 | $30.6037 | $34.1428 | $32.5370 | $36.7217 | $33.8723 | $34.5427 | $39.6439 | $34.3451 | $30.4130 | $38.8873 | $31.0336 | $30.5114 | |||
| 34 | Jim's | 1/25/20 | $31.2934 | $32.4184 | $39.8055 | $33.4894 | $37.8142 | $35.4615 | $33.8107 | $30.2800 | $34.1914 | $36.8801 | $39.6068 | $38.2372 | $32.5728 | $30.9013 | $37.1015 | $38.1793 | $36.2732 | $30.2369 | $32.4149 | $33.9298 | $36.4215 | $34.3467 | $31.9003 | $37.4603 | |||
| 35 | Jim's | 1/26/20 | $31.1609 | $32.6604 | $36.6971 | $35.5410 | $37.9386 | $36.2182 | $31.4790 | $35.7001 | $37.6024 | $34.8896 | $30.9885 | $30.3460 | $36.3401 | $34.1692 | $35.9397 | $38.0458 | $34.1457 | $32.0318 | $36.8075 | $37.4445 | $38.2211 | $39.9552 | $35.2849 | $34.7522 | |||
| 36 | Jim's | 1/27/20 | $34.6709 | $35.0040 | $38.1503 | $36.4289 | $37.6776 | $36.8891 | $38.6220 | $30.7563 | $30.1882 | $38.2195 | $37.4940 | $36.3276 | $36.2197 | $31.7648 | $34.4869 | $34.5080 | $36.2933 | $36.2554 | $36.4904 | $36.3266 | $32.4929 | $34.6281 | $35.5695 | $31.7147 | |||
| 37 | Jim's | 1/28/20 | $39.2266 | $38.0612 | $36.5003 | $38.0427 | $31.9069 | $32.1192 | $31.6769 | $32.3303 | $36.5352 | $32.3268 | $32.0847 | $38.8740 | $31.6084 | $31.2956 | $32.1846 | $33.6421 | $37.4366 | $37.5084 | $39.6476 | $33.2715 | $34.6157 | $32.0505 | $38.4477 | $30.2006 | |||
| 38 | Jim's | 1/29/20 | $30.8837 | $37.9341 | $36.4417 | $32.8460 | $32.2554 | $37.8806 | $33.7643 | $36.2344 | $33.6071 | $35.5945 | $30.9820 | $38.3204 | $32.0183 | $31.7927 | $36.6029 | $38.1037 | $31.1445 | $34.7036 | $34.9657 | $36.3955 | $31.9082 | $34.8367 | $32.4617 | $38.7273 | |||
| 39 | Jim's | 1/30/20 | $31.3281 | $33.6839 | $34.7124 | $34.7524 | $37.1139 | $35.2116 | $35.8902 | $30.6024 | $30.3373 | $35.1520 | $38.6479 | $32.0262 | $38.3843 | $37.4523 | $37.0978 | $30.2138 | $33.4748 | $31.3273 | $33.9663 | $32.5515 | $36.5159 | $38.6735 | $33.5722 | $34.3503 | |||
| 40 | Jim's | 1/31/20 | $39.8686 | $38.5584 | $33.5349 | $38.3412 | $31.5834 | $33.2544 | $31.9994 | $37.1147 | $37.4639 | $35.8785 | $36.7846 | $31.8655 | $39.3324 | $37.3474 | $32.7483 | $31.7100 | $35.2202 | $39.4860 | $31.8024 | $39.1688 | $36.5078 | $38.6498 | $39.8850 | $37.3983 | |||
Hourly_Shoe_Prices | |||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D4 | D4 | =AVERAGE(D10:AA40) |
| D5 | D5 | =AVERAGE($K$11:$Z$12,$K$15:$Z$19,$K$22:$Z$26,$K$29:$Z$33,$K$36:$Z$40) |
| D6 | D6 | =AVERAGE($D$10:$AA$10,$D$13:$AA$14,$D$20:$AA$21,$D$27:$AA$28,$D$34:$AA$35,$D$11:$J$12,$AA$11:$AA$12,$D$15:$J$19,$AA$15:$AA$19,$D$22:$J$26,$AA$22:$AA$26,$D$29:$J$33,$AA$29:$AA$33,$D$36:$J$40,$AA$36:$AA$40) |
| E9:AA9 | E9 | =D9+1 |
| C11:C40 | C11 | =C10+1 |
I don't really know the given sample calculations are meant to push my approach in one way or another, but I approached it a bit different and I wanted someone to let me know if I'm going about this wrong. I'm absolutely an Excel novice and have less than 2-3 days of Excel knowledge.
Here are the steps I took.
1. Selected the store, date, and all of the hours column as well as all of the data below to then insert a PivotTable based on that data in a new sheet.
2. Please reference the attached picture for how I set up my Pivot Table but essentially I moved "Stores" into "Rows", "Months" into "Columns", and "Date" into "Filters". I moved each of the 24 Hour fields into "Values" and changed the field setting to calculate the average for each hour. I also put the "Values" field into the "Rows" section of the PivotTable tool on the right.
So now I have the average price for each hour for each month for both Jim and Jane, but something tells me there's a much easier and cleaner way to display this data. But if I got this far and made it look like this, I have no idea what I would need to do for to calculate the peak and non peak hours for each store. My thinking is that if I can get this pivot table to look a bit more cleaner and well displayed, why not just make 2 more pivot tables with the same dataset, and use the "filter" function of a pivot table to exclude the specific parameters that define peak vs non-peak hours.
To be honest, this exercise is part of a job interview process, and I was told that there really isn't a right answer per se and they just want to gauge how I would approach a problem like this. I was pretty transparent with them about not having any actual excel experience, so I guess they're use these exercise to see if I'm fit for another interview!
Anyways, I'll keep researching, but gosh does it suck to have to do this exercise from a MacBook! I really appreciate any help or suggestions provided regarding this problem, and thank you for reading!
