# Average by Day of the Week

#### alexfooty

##### Board Regular
Hi
In the following I want to know the average for each day of the week based on the dates in Column B and the values in column C - but ignoring any blank cells
The actual columns are much longer than this and run into several months - this is just an example.
A formula would be greatly appreciated. Thank you

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Joe4

I think we need further clarification.
You say the average for each day, but in your example, each day only appears to have one entry. So the average of one single value is just that value.
Or are days in your list really listed more than once?

Or do you actually have just one line per day, put multiple columns of data (i.e. like column D, etc).?
If so, how far does it go out (what is the last possible column)?

#### jasonb75

##### Well-known Member
Are you saying that you want the average in column C for each weekday (e.g average for all Mondays), or should it be the daily average for columns C and D, not just column C as stated in your post?

#### Joe4

Are you saying that you want the average in column C for each weekday (e.g average for all Mondays), or should it be the daily average for columns C and D, not just column C as stated in your post?
Or that possibility (I missed that possibility in my list of questions)?

As you can see, it isn't entirely clear to us what you are trying average here.

#### alexfooty

##### Board Regular

Sorry for the confusion. Let's forget about Column D - if I have a formula for column C, I can change that to apply to Column D if nexessary.
I need to find the average for all Mondays, all Tuesdays etc but ignoring the blanks. So if the values for Mondays were 96, 98, blank, 100 - average would be 98 (294/3 - not 294/4)
Hope this is a bit clearer!

#### jasonb75

##### Well-known Member
Something like this?
Book1
BCDEF
1DayValue
214-Mar98Sunday98
315-Mar93Monday94.5
416-Mar98Tuesday49
517-Mar88Wednesday91.5
618-Mar98Thursday49
719-MarFriday44.5
820-Mar97Saturday97.5
921-Mar98
1022-Mar96
1123-Mar
1224-Mar95
1325-Mar
1426-Mar89
1527-Mar98
Sheet4
Cell Formulas
RangeFormula
F2:F8F2=AVERAGE(IF(WEEKDAY(\$B\$2:\$B\$15,1)=ROWS(F\$2:F2),\$C\$2:\$C\$15))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### alexfooty

##### Board Regular

=AVERAGE(IF(WEEKDAY(\$B\$2:\$B\$15,1)=ROWS(F\$2:F2),\$C\$2:\$C\$15))

If you ignore the blanks, Tuesday should be 98, Thursday - 98 and Friday - 89
Also, future dates eg tomorrow etc would be blank until filled in. When I fill in tomorrow's date I would like the daily average to automatically update.
I must admit I'm finding it difficult to explain what I want while trying see it from someone else's point of view.

#### Joe4

Following Jason's example, put this formula in cell F2 and copy down:
Excel Formula:
``=SUMPRODUCT(--(WEEKDAY(B2:B15)=(ROW()-1)),--(C2:C15))/SUMPRODUCT(--(WEEKDAY(B2:B15)=(ROW()-1)),--(C2:C15<>""))``
It will ignore any blank values.

#### jasonb75

##### Well-known Member
If you ignore the blanks, Tuesday should be 98, Thursday - 98 and Friday - 89
That was my fault for not checking the individual results. Average alone ignores empty cells, I forgot that the empty cells would return 0 to the array.

If you can add a helper column it would simplify it significantly.
Book1
ABCDEF
1DayValue
2Sunday14-Mar98Sunday98
3Monday15-Mar93Monday94.5
4Tuesday16-Mar98Tuesday98
5Wednesday17-Mar88Wednesday91.5
6Thursday18-Mar98Thursday98
7Friday19-MarFriday89
8Saturday20-Mar97Saturday97.5
9Sunday21-Mar98
10Monday22-Mar96
11Tuesday23-Mar
12Wednesday24-Mar95
13Thursday25-Mar
14Friday26-Mar89
15Saturday27-Mar98
Sheet4
Cell Formulas
RangeFormula
F2:F8F2=AVERAGEIFS(\$C\$2:\$C\$15,\$A\$2:\$A\$15,E2)
A2:A15A2=TEXT(B2,"dddd")

#### alexfooty

##### Board Regular
Joe4 - that formula works a treat - perfect. Thank you so much,
Thank you to everyone for their contributions, it's greatly appreciated.
That was my fault for not checking the individual results. Average alone ignores empty cells, I forgot that the empty cells would return 0 to the array.

If you can add a helper column it would simplify it significantly.
Book1
ABCDEF
1DayValue
2Sunday14-Mar98Sunday98
3Monday15-Mar93Monday94.5
4Tuesday16-Mar98Tuesday98
5Wednesday17-Mar88Wednesday91.5
6Thursday18-Mar98Thursday98
7Friday19-MarFriday89
8Saturday20-Mar97Saturday97.5
9Sunday21-Mar98
10Monday22-Mar96
11Tuesday23-Mar
12Wednesday24-Mar95
13Thursday25-Mar
14Friday26-Mar89
15Saturday27-Mar98
Sheet4
Cell Formulas
RangeFormula
F2:F8F2=AVERAGEIFS(\$C\$2:\$C\$15,\$A\$2:\$A\$15,E2)
A2:A15A2=TEXT(B2,"dddd")
Jasonb75
Would your formula work if the months were in separate columns as follows

Replies
8
Views
107
Replies
9
Views
93
Replies
2
Views
88
Replies
1
Views
348
Replies
1
Views
150

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,482
Messages
5,770,349
Members
425,612
Latest member
martinijr

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