# Calculating average based on days in a column

#### whynot

##### Board Regular
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Fluff

##### MrExcel MVP, Moderator
+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
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

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))),"")

#### Fluff

##### MrExcel MVP, Moderator
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

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
Yes that would be possible, but you had better start a new thread.

#### whynot

##### Board Regular
will do.

Again thank you for your help!!

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback.

Replies
6
Views
82
Replies
3
Views
132
Replies
4
Views
47
Replies
1
Views
243
Replies
1
Views
154

1,126,957
Messages
5,621,822
Members
415,859
Latest member
Vain

### 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.

### Which adblocker are you using?

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

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