Average by Day of the Week

[alexfooty]

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

 

[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]

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]

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]

Something like this?

Book1
	B	C	D	E	F
1	Day	Value
2	14-Mar	98		Sunday	98
3	15-Mar	93		Monday	94.5
4	16-Mar	98		Tuesday	49
5	17-Mar	88		Wednesday	91.5
6	18-Mar	98		Thursday	49
7	19-Mar			Friday	44.5
8	20-Mar	97		Saturday	97.5
9	21-Mar	98
10	22-Mar	96
11	23-Mar
12	24-Mar	95
13	25-Mar
14	26-Mar	89
15	27-Mar	98
Sheet4

Cell Formulas
Range	Formula
F2:F8	F2	=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]

=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:

=SUMPRODUCT(--(WEEKDAY(B2:B15)=(ROW()-1)),--(C2:C15))/SUMPRODUCT(--(WEEKDAY(B2:B15)=(ROW()-1)),--(C2:C15<>""))

It will ignore any blank values.

 

[jasonb75]

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
	A	B	C	D	E	F
1		Day	Value
2	Sunday	14-Mar	98		Sunday	98
3	Monday	15-Mar	93		Monday	94.5
4	Tuesday	16-Mar	98		Tuesday	98
5	Wednesday	17-Mar	88		Wednesday	91.5
6	Thursday	18-Mar	98		Thursday	98
7	Friday	19-Mar			Friday	89
8	Saturday	20-Mar	97		Saturday	97.5
9	Sunday	21-Mar	98
10	Monday	22-Mar	96
11	Tuesday	23-Mar
12	Wednesday	24-Mar	95
13	Thursday	25-Mar
14	Friday	26-Mar	89
15	Saturday	27-Mar	98
Sheet4

Cell Formulas
Range	Formula
F2:F8	F2	=AVERAGEIFS($C$2:$C$15,$A$2:$A$15,E2)
A2:A15	A2	=TEXT(B2,"dddd")

 

[alexfooty]

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
	A	B	C	D	E	F
1		Day	Value
2	Sunday	14-Mar	98		Sunday	98
3	Monday	15-Mar	93		Monday	94.5
4	Tuesday	16-Mar	98		Tuesday	98
5	Wednesday	17-Mar	88		Wednesday	91.5
6	Thursday	18-Mar	98		Thursday	98
7	Friday	19-Mar			Friday	89
8	Saturday	20-Mar	97		Saturday	97.5
9	Sunday	21-Mar	98
10	Monday	22-Mar	96
11	Tuesday	23-Mar
12	Wednesday	24-Mar	95
13	Thursday	25-Mar
14	Friday	26-Mar	89
15	Saturday	27-Mar	98
Sheet4

Cell Formulas
Range	Formula
F2:F8	F2	=AVERAGEIFS($C$2:$C$15,$A$2:$A$15,E2)
A2:A15	A2	=TEXT(B2,"dddd")

Jasonb75
Would your formula work if the months were in separate columns as follows