Not sure what function to utilize.
Thought I could use XLOOKUP but not sure.
I need to calculate the average of seven number based on day of week.
Column A includes the date, Column B the data I need to calculate the average for, Column E are days of the week, and Column F is the output column
See screenshot below.
I would like to average the seven numbers for the week starting on Sunday.
For example, in column F14 I would like to determine the average for the data in B8 to B14. I would like to write a formula that allows Excel to determine that Column E indicates Sunday and provide an average for the number for that day in Column B and the six previous. I have data from March 1, 1960 to September 6, 2020. Therefore, calculating manually would take a significant amount of time.
Thank you in advance for your help.
Thought I could use XLOOKUP but not sure.
I need to calculate the average of seven number based on day of week.
Column A includes the date, Column B the data I need to calculate the average for, Column E are days of the week, and Column F is the output column
See screenshot below.
I would like to average the seven numbers for the week starting on Sunday.
For example, in column F14 I would like to determine the average for the data in B8 to B14. I would like to write a formula that allows Excel to determine that Column E indicates Sunday and provide an average for the number for that day in Column B and the six previous. I have data from March 1, 1960 to September 6, 2020. Therefore, calculating manually would take a significant amount of time.
Thank you in advance for your help.