Calculating average based on days in a column

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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.

1600109892970.png

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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,903
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABCDEF
1
201/03/1960-1.41256Tuesday 
302/03/1960-1.36104Wednesday 
403/03/1960-2.62921Thursday 
504/03/1960-1.39656Friday 
605/03/1960-1.83186Saturday 
706/03/1960-2.79922Sunday-1.90508
807/03/1960-1.87263Monday 
908/03/1960-1.27769Tuesday 
1009/03/1960-2.17864Wednesday 
1110/03/1960-2.92111Thursday 
1211/03/1960-2.68051Friday 
1312/03/1960-1.03618Saturday 
1413/03/1960-1.24494Sunday-1.88739
1514/03/1960-2.69544Monday 
1615/03/1960-2.66468Tuesday 
1716/03/1960-2.38535Wednesday 
1817/03/1960-2.7024Thursday 
1918/03/1960-1.86111Friday 
2019/03/1960-2.82573Saturday 
2120/03/1960-2.60112Sunday-2.53369
2221/03/1960-2.43287Monday 
2322/03/1960-1.64859Tuesday 
2423/03/1960-1.61891Wednesday 
2524/03/1960-1.78218Thursday 
2625/03/1960-2.87654Friday 
2726/03/1960-1.58493Saturday 
2827/03/1960-2.32122Sunday-2.03789
2928/03/1960-2.08111Monday 
3029/03/1960-2.53826Tuesday 
3130/03/1960-1.73751Wednesday 
3231/03/1960-1.47296Thursday 
3301/04/1960-2.70999Friday 
Main
Cell Formulas
RangeFormula
E2:E33E2=TEXT(A2,"dddd")
F2:F33F2=IF(E2="Sunday",AVERAGE(FILTER($B$2:$B$33,ISOWEEKNUM($A$2:$A$33)=ISOWEEKNUM(A2))),"")
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for your help.

I am doing something wrong.

It works when i don't extend the formula beyond a certain row.

I have data that extends to row 22,000

If i edit the formula to say row 4900 it does not work.

here is how i edited the formula

=IF(E7="Sunday",AVERAGE(FILTER($B$2:$B$4900,ISOWEEKNUM($A$2:$A$4900)=ISOWEEKNUM(A7))),"")

the screenshots below are the answers i get when i use the formula exactly how you wrote it. the numbers are correct

1600111909735.png


Output when i edit the formula - using the following formula

=IF(E7="Sunday",AVERAGE(FILTER($B$2:$B$4900,ISOWEEKNUM($A$2:$A$4900)=ISOWEEKNUM(A7))),"")

1600111993898.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,903
Office Version
  1. 365
Platform
  1. Windows
If you have multiple years try
Excel Formula:
=IF(E2="Sunday",AVERAGE(FILTER($B$2:$B$33,(ISOWEEKNUM($A$2:$A$33)=ISOWEEKNUM(A2))*(YEAR($A$2:$A$33)=YEAR(A2)))),"")
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you very much!!

That was extremely helpful and saved me a ton of time.

You are amazing at this.

Is it possible to create a similar formula if I wanted to average for a quarter. For the period between 1/1 - 3/31, 4/1- 6/30, 7/1 - 9/30, and 10/1 - 12/31.

Really appreciate the help!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,903
Office Version
  1. 365
Platform
  1. Windows
Yes that would be possible, but you had better start a new thread.
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
will do.

Again thank you for your help!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,903
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,814
Messages
5,542,643
Members
410,565
Latest member
sebby_joe
Top