Calculating average based on days in a column

whynot

Board Regular
Joined
Jun 27, 2009
Messages
111
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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))),"")
 
Upvote 0
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
 
Upvote 0
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)))),"")
 
Upvote 0
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!!
 
Upvote 0
Yes that would be possible, but you had better start a new thread.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top