Average by Day of the Week

alexfooty

Board Regular
Joined
Dec 30, 2018
Messages
97
Office Version
  1. 2016
Platform
  1. Windows
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
Image 1.png
 

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,951
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,951
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2018
Messages
97
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2018
Messages
97
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,951
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2018
Messages
97
Office Version
  1. 2016
Platform
  1. Windows
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

Image 2.png
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top