Hello,
I had a lot of data, with multiple headers, which I unpivoted using PowerQuery. The resulting PivotTable I got was:
Column A (Restaurants): McDonald's, KFC, Burger King, etc.
Column B (Location): Seattle, San Francisco, New York, etc.
Column C (Year): 2024, 2023, 2022, etc.
Column D (Week): 1, 2, 3, ...., 52, 53
Column E (Time): 00:00:00, 00:01:00, ...., 23:58:00, 23:59:00
Column F (Sale): *this is various prices of sales recorded at the corresponding times in Column E*
Although Column A is the restaurant name, there can be numerous rows of sales at a McDonald's at 13:00:00 due to the varying locations in Column B.
For this reason, I am trying to get the sales price at 13:00:00 (for example), during week 1 of 2024, for all of the McDonald's restaurants. If we assume the sale at this time at the New York restaurant was $15.50, the Seattle restaurant was $12.75, and the San Francisco restaurant was $20; the average would be ~$16.08.
Of course, I would then like to do this for each timestamp for each week in each year for all of the restaurants in Column A. Once I have the average sale for each restaurant location at each timestamp during each week of each year, I would like to sum these averages for every restaurant (McDonald's, KFC, etc.) at each time (00:00:00, 00:01:00, etc ) for each week (1, 2, etc.) during each year (3024, 2023, etc.).
I did create multiple columns. For example, the column for McDonald's was called "McDonald's Average Sales" and used the following formula:
=AVERAGEIFS($F:$F, $A:$A, "="&"McDonald's", $C:$C, "="&[@Year], $D:$D, "="&[@Week], $E:$E, "="&[@Time])
It seems like this worked as intended (although it displays these values throughout each row even if the A cell in a row says "KFC" or "Burger King", instead of "McDonald's") but, I noticed that due to the inclusion of AVERAGEIFS, my Excel takes quite a long time to be usable as it's calculating multiple threads.
I understand that I can turn off automatic calculations to quick-fix this but, I was wondering if anyone would know of a different and more productive method of achieving what I want this Excel to do?
Any feedback is very grateful! Also, apologies for the long-winded post, this is my first thread and I wanted to provide as much information as possible!
Thank you in advance!
I had a lot of data, with multiple headers, which I unpivoted using PowerQuery. The resulting PivotTable I got was:
Column A (Restaurants): McDonald's, KFC, Burger King, etc.
Column B (Location): Seattle, San Francisco, New York, etc.
Column C (Year): 2024, 2023, 2022, etc.
Column D (Week): 1, 2, 3, ...., 52, 53
Column E (Time): 00:00:00, 00:01:00, ...., 23:58:00, 23:59:00
Column F (Sale): *this is various prices of sales recorded at the corresponding times in Column E*
Although Column A is the restaurant name, there can be numerous rows of sales at a McDonald's at 13:00:00 due to the varying locations in Column B.
For this reason, I am trying to get the sales price at 13:00:00 (for example), during week 1 of 2024, for all of the McDonald's restaurants. If we assume the sale at this time at the New York restaurant was $15.50, the Seattle restaurant was $12.75, and the San Francisco restaurant was $20; the average would be ~$16.08.
Of course, I would then like to do this for each timestamp for each week in each year for all of the restaurants in Column A. Once I have the average sale for each restaurant location at each timestamp during each week of each year, I would like to sum these averages for every restaurant (McDonald's, KFC, etc.) at each time (00:00:00, 00:01:00, etc ) for each week (1, 2, etc.) during each year (3024, 2023, etc.).
I did create multiple columns. For example, the column for McDonald's was called "McDonald's Average Sales" and used the following formula:
=AVERAGEIFS($F:$F, $A:$A, "="&"McDonald's", $C:$C, "="&[@Year], $D:$D, "="&[@Week], $E:$E, "="&[@Time])
It seems like this worked as intended (although it displays these values throughout each row even if the A cell in a row says "KFC" or "Burger King", instead of "McDonald's") but, I noticed that due to the inclusion of AVERAGEIFS, my Excel takes quite a long time to be usable as it's calculating multiple threads.
I understand that I can turn off automatic calculations to quick-fix this but, I was wondering if anyone would know of a different and more productive method of achieving what I want this Excel to do?
Any feedback is very grateful! Also, apologies for the long-winded post, this is my first thread and I wanted to provide as much information as possible!
Thank you in advance!
