Averaging weekday totals in excel

[iclancy]

Hello,

I'd like to find average ridership counts by day from my table. This table updates based on the current month so I need a formula that can keep up. I have researched several different formulas to do this, but each one I've found ends up counting all of the zeros from the "Ridership by Day" table in the average formula, instead of just the total number of that day. For example, the formula in the below screenshot ends up dividing the total counts for Mondays for the month (17) by 31, so 17/31 = 0.6129. How can I ensure that the formula would correctly divide by the total number of Mondays (17/4 in the example case)?

Here are a couple of the formulas I've tried:

=AVERAGE(IF(WEEKDAY(B28)=WEEKDAY($B$28:$B$58),$C$28:$C$58))
=AVERAGE(IF(DAY(B28:B58)=1,C28:C58))

I've also tried referencing column E for the "serial number" argument in the WEEKDAY function, but that returns a #VALUE! error.

Thanks in advance for your help. Let me know if you need me to provide more information.

 

[etaf]

=AVERAGE(IF(WEEKDAY($B$28:$B$58,2)=1,$C$28:$C$58))

if you put , 1 ,2 , 3 4 etc against the days - then you can reference the cell

Book1
	A	B	C	D	E	F	G
1
2	Tue	1/4/22	1		1	Monday	12
3	Wed	1/5/22	14		2	Tuesday	3
4	Thu	1/6/22			3	Wednesday	14
5	Fri	1/7/22			4	Thursday	0
6	Wed	1/5/22	14		5	Friday	0
7	Thu	1/6/22			6	Saturday	0
8	Fri	1/7/22			7	Sunday	0
9	Sat	1/8/22
10	Sun	1/9/22
11	Mon	1/10/22	12
12	Tue	1/11/22	5
13	Wed	1/12/22	14
14	Thu	1/13/22
15	Fri	1/14/22
16
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=AVERAGE(IF(WEEKDAY($B$2:$B$15,2)=E2,$C$2:$C$15))

enter as an array formula
Shift + Ctrl + Enter keys together


Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[iclancy]

Thanks for the tip. Here's a mini-sheet. The end result I am trying to get is having the "Avg" column display the average ridership by day of the week, pulled from the "Ridership by Day" table, similar to how the "Total" column next to it displays the total ridership by day using the SUMPRODUCT function. Ideally I'm looking for a way to do this without adding extra columns, if possible.

In the below sheet, the correct average of 7 is manually entered for Monday; the other formulas I've tried are in the Tuesday and Wednesday cells below it.

Eastern Ridership Report Template-v3.1-test2.xlsx
	A	B	C	D	E	F	G
6	Version	3.1
7
8
9
10	Invoice Month (mm/dd/yyyy)	1-Jul-2021
11	Service Days this Month	31
12
13		Ridership by Day
14		Day	Ridership			Total	Avg
15		Thursday, July 1, 2021	0		Monday	21	7
16		Friday, July 2, 2021	0		Tuesday	14	0
17		Saturday, July 3, 2021	0		Wednesday	0	#VALUE!
18		Sunday, July 4, 2021	0		Thursday	0
19		Monday, July 5, 2021	6		Friday	0
20		Tuesday, July 6, 2021	3		Saturday	0
21		Wednesday, July 7, 2021	0		Sunday	0
22		Thursday, July 8, 2021	0
23		Friday, July 9, 2021	0
24		Saturday, July 10, 2021	0
25		Sunday, July 11, 2021	0
26		Monday, July 12, 2021	9
27		Tuesday, July 13, 2021	5
28		Wednesday, July 14, 2021	0
29		Thursday, July 15, 2021	0
30		Friday, July 16, 2021	0
31		Saturday, July 17, 2021	0
32		Sunday, July 18, 2021	0
33		Monday, July 19, 2021	6
34		Tuesday, July 20, 2021	6
35		Wednesday, July 21, 2021	0
36		Thursday, July 22, 2021	0
37		Friday, July 23, 2021	0
38		Saturday, July 24, 2021	0
39		Sunday, July 25, 2021	0
40		Monday, July 26, 2021	0
41		Tuesday, July 27, 2021	0
42		Wednesday, July 28, 2021	0
43		Thursday, July 29, 2021	0
44		Friday, July 30, 2021	0
45		Saturday, July 31, 2021	0
46			35
Summary

Cell Formulas
Range	Formula
B11	B11	=DAY(DATE(YEAR(B10),MONTH(B10)+1,1)-1)
G16	G16	=AVERAGE(IF(DAY(B15:B45)=1,C15:C45))
G17	G17	=AVERAGE(IF(WEEKDAY(E17)=WEEKDAY($B$15:$B$45),$C$15:$C$45))
C15	C15	=SUM('BND to ONT'!N2:N3,'ONT to BND'!N2:N3)
C16	C16	=SUM('BND to ONT'!N5:N6,'ONT to BND'!N5:N6)
C17	C17	=SUM('BND to ONT'!N8:N9,'ONT to BND'!N8:N9)
C18	C18	=SUM('BND to ONT'!N11:N12,'ONT to BND'!N11:N12)
C19	C19	=SUM('BND to ONT'!N14:N15,'ONT to BND'!N14:N15)
F15:F21	F15	=SUMPRODUCT((TEXT(B$15:B$45, "ddddddddd")=E15)*((C$15:C$45)))
C21	C21	=SUM('BND to ONT'!N20:N21,'ONT to BND'!N20:N21)
C22	C22	=SUM('BND to ONT'!N23:N24,'ONT to BND'!N23:N24)
C23	C23	=SUM('BND to ONT'!N26:N27,'ONT to BND'!N26:N27)
C24	C24	=SUM('BND to ONT'!N29:N30,'ONT to BND'!N29:N30)
C25	C25	=SUM('BND to ONT'!N32:N33,'ONT to BND'!N32:N33)
C26	C26	=SUM('BND to ONT'!N35:N36,'ONT to BND'!N35:N36)
C28	C28	=SUM('BND to ONT'!N41:N42,'ONT to BND'!N41:N42)
C29	C29	=SUM('BND to ONT'!N44:N45,'ONT to BND'!N44:N45)
C30	C30	=SUM('BND to ONT'!N47:N48,'ONT to BND'!N47:N48)
C31	C31	=SUM('BND to ONT'!N50:N51,'ONT to BND'!N50:N51)
C32	C32	=SUM('BND to ONT'!N53:N54,'ONT to BND'!N53:N54)
B15	B15	=B10
B16:B43	B16	=IF(MONTH(B15+1)=MONTH($B$10),B15+1,(""))
B44	B44	=IF(MONTH(B42+2)=MONTH($B$10),B42+2,(""))
B45	B45	=IF(MONTH(B42+3)=MONTH($B$10),B42+3,(""))
C35	C35	=SUM('BND to ONT'!N62:N63,'ONT to BND'!N62:N63)
C36	C36	=SUM('BND to ONT'!N65:N66,'ONT to BND'!N65:N66)
C37	C37	=SUM('BND to ONT'!N68:N69,'ONT to BND'!N68:N69)
C38	C38	=SUM('BND to ONT'!N71:N72,'ONT to BND'!N71:N72)
C39	C39	=SUM('BND to ONT'!N74:N75,'ONT to BND'!N74:N75)
C40	C40	=SUM('BND to ONT'!N77:N78,'ONT to BND'!N77:N78)
C41	C41	=SUM('BND to ONT'!N80:N81,'ONT to BND'!N80:N81)
C42	C42	=SUM('BND to ONT'!N83:N84,'ONT to BND'!N83:N84)
C43	C43	=SUM('BND to ONT'!N86:N87,'ONT to BND'!N86:N87)
C44	C44	=SUM('BND to ONT'!N89:N90,'ONT to BND'!N89:N90)
C45	C45	=SUM('BND to ONT'!N92:N93,'ONT to BND'!N92:N93)
C46	C46	=SUM(C15:C45)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B15:B46	Expression	=$B15="Saturday, July 3, 2021"	text	NO
C15:C45	Cell Value	<1	text	NO
C19	Expression	=OR(#REF!=7,#REF!=1)	text	NO
C20	Expression	=OR(#REF!=7,#REF!=1)	text	NO
C21	Expression	=OR(#REF!=7,#REF!=1)	text	NO
C22	Expression	=OR(#REF!=7,#REF!=1)	text	NO
C23	Expression	=OR(#REF!=7,#REF!=1)	text	NO

I hope this helps.

 

[etaf]

how about
=AVERAGE(IF(WEEKDAY($B$15:$B$45,2)=MATCH(E15,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),$C$15:$C$45))

Book1
	A	B	C	D	E	F	G
6	Version	3.1
7
8
9
10	Invoice Month (mm/dd/yyyy)	7/1/21
11	Service Days this Month	31
12
13		Ridership by Day
14		Day	Ridership			Total	Avg
15		7/1/21	1		Monday	20	5
16		7/2/21	2		Tuesday	24	6
17		7/3/21	3		Wednesday	28	7
18		7/4/21	4		Thursday	5	1
19		7/5/21	5		Friday	10	2
20		7/6/21	6		Saturday	15	3
21		7/7/21	7		Sunday	16	4
22		7/8/21	1
23		7/9/21	2
24		7/10/21	3
25		7/11/21	4
26		7/12/21	5
27		7/13/21	6
28		7/14/21	7
29		7/15/21	1
30		7/16/21	2
31		7/17/21	3
32		7/18/21	4
33		7/19/21	5
34		7/20/21	6
35		7/21/21	7
36		7/22/21	1
37		7/23/21	2
38		7/24/21	3
39		7/25/21	4
40		7/26/21	5
41		7/27/21	6
42		7/28/21	7
43		7/29/21	1
44		7/30/21	2
45		7/31/21	3
Sheet1

Cell Formulas
Range	Formula
B11	B11	=DAY(DATE(YEAR(B10),MONTH(B10)+1,1)-1)
F15:F21	F15	=SUMPRODUCT((TEXT(B$15:B$45, "ddddddddd")=E15)*((C$15:C$45)))
G15:G21	G15	=AVERAGE(IF(WEEKDAY($B$15:$B$45,2)=MATCH(E15,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),$C$15:$C$45))
B15	B15	=B10
B16:B43	B16	=IF(MONTH(B15+1)=MONTH($B$10),B15+1,(""))
B44	B44	=IF(MONTH(B42+2)=MONTH($B$10),B42+2,(""))
B45	B45	=IF(MONTH(B42+3)=MONTH($B$10),B42+3,(""))

 

[iclancy]

Thanks! I'm not sure why, but the formula is not working for me when I plug it in to my spreadsheet. It still returns a 0 for Monday and all the other days of the week. I thought it might have something to do with the way my dates are displayed in the ridership table, so I tried changing them to Short Date format to match your table but still got the same result. Any idea as to why the same formula wouldn't work for my sheet?

 

[etaf]

i notice you are using 2016 - is that the case

its an array formula so it will need to be entered using
Ctrl+Shift+Enter
and then you should see
{=AVERAGE(IF(WEEKDAY($B$15:$B$45,2)=MATCH(E15,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),$C$15:$C$45))}

 

[iclancy]

Yes, that did it! Thanks so much for your help.

 

[etaf]

you are welcome

 

[iclancy]

Hi,

I've noticed another (hopefully!) minor issue with the new averaging formula above that I am having trouble fixing. I mentioned above that the "Ridership by Day" table updates based on the current month. When changed to a month with 30 days, the averaging formulas for all days in the "Average" column return #VALUE! errors, due to the fact that the last day's cell, B58, now displays as a blank because of previous formatting I implemented for neatness (without this formatting, the cell displays the first day of the following month, which is undesired). When evaluating the WEEKDAY function in the averaging formula, it encounters the blank cell with no date and doesn't know what to do with it. See cell B58 in the mini-sheet:

Eastern Ridership Report Template-v3.2-not yet sent.xlsx
	B	C	D	E	F	G
26	Ridership by Day
27	Day	Ridership		Daily Totals & Averages
28	Thursday, September 1, 2022	0			Total	Average
29	Friday, September 2, 2022	0		Monday	24	6
30	Saturday, September 3, 2022	0		Tuesday	0	0
31	Sunday, September 4, 2022	0		Wednesday	0	0
32	Monday, September 5, 2022	6		Thursday	0	0
33	Tuesday, September 6, 2022	0		Friday	0	0
34	Wednesday, September 7, 2022	0		Saturday	0	0
35	Thursday, September 8, 2022	0		Sunday	0	0
36	Friday, September 9, 2022	0
37	Saturday, September 10, 2022	0
38	Sunday, September 11, 2022	0
39	Monday, September 12, 2022	9
40	Tuesday, September 13, 2022	0
41	Wednesday, September 14, 2022	0
42	Thursday, September 15, 2022	0
43	Friday, September 16, 2022	0
44	Saturday, September 17, 2022	0
45	Sunday, September 18, 2022	0
46	Monday, September 19, 2022	2
47	Tuesday, September 20, 2022	0
48	Wednesday, September 21, 2022	0
49	Thursday, September 22, 2022	0
50	Friday, September 23, 2022	0
51	Saturday, September 24, 2022	0
52	Sunday, September 25, 2022	0
53	Monday, September 26, 2022	7
54	Tuesday, September 27, 2022	0
55	Wednesday, September 28, 2022	0
56	Thursday, September 29, 2022	0
57	Friday, September 30, 2022	0
58		0
59		24
Summary

Cell Formulas
Range	Formula
F29:F35	F29	=SUMPRODUCT((TEXT(B$28:B$58, "ddddddddd")=E29)*((C$28:C$58)))
G29:G35	G29	=IF(B58="",AVERAGE(IF(WEEKDAY($B$28:$B$57,2)=MATCH(E29,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),$C$28:$C$57)),AVERAGE(IF(WEEKDAY($B$28:$B$58,2)=MATCH(E29,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),$C$28:$C$58)))
B28	B28	=B10
B29:B56	B29	=IF(MONTH(B28+1)=MONTH($B$10),B28+1,(""))
B57	B57	=IF(MONTH(B55+2)=MONTH($B$10),B55+2,(""))
B58	B58	=IF(MONTH(B55+3)=MONTH($B$10),B55+3,(""))
C28	C28	=SUM('BND to ONT'!N2:N3,'ONT to BND'!N2:N3)
C29	C29	=SUM('BND to ONT'!N5:N6,'ONT to BND'!N5:N6)
C30	C30	=SUM('BND to ONT'!N8:N9,'ONT to BND'!N8:N9)
C31	C31	=SUM('BND to ONT'!N11:N12,'ONT to BND'!N11:N12)
C32	C32	=SUM('BND to ONT'!N14:N15,'ONT to BND'!N14:N15)
C33	C33	=SUM('BND to ONT'!N17:N18,'ONT to BND'!N17:N18)
C34	C34	=SUM('BND to ONT'!N20:N21,'ONT to BND'!N20:N21)
C35	C35	=SUM('BND to ONT'!N23:N24,'ONT to BND'!N23:N24)
C36	C36	=SUM('BND to ONT'!N26:N27,'ONT to BND'!N26:N27)
C37	C37	=SUM('BND to ONT'!N29:N30,'ONT to BND'!N29:N30)
C38	C38	=SUM('BND to ONT'!N32:N33,'ONT to BND'!N32:N33)
C39	C39	=SUM('BND to ONT'!N35:N36,'ONT to BND'!N35:N36)
C40	C40	=SUM('BND to ONT'!N38:N39,'ONT to BND'!N38:N39)
C41	C41	=SUM('BND to ONT'!N41:N42,'ONT to BND'!N41:N42)
C42	C42	=SUM('BND to ONT'!N44:N45,'ONT to BND'!N44:N45)
C43	C43	=SUM('BND to ONT'!N47:N48,'ONT to BND'!N47:N48)
C44	C44	=SUM('BND to ONT'!N50:N51,'ONT to BND'!N50:N51)
C45	C45	=SUM('BND to ONT'!N53:N54,'ONT to BND'!N53:N54)
C46	C46	=SUM('BND to ONT'!N56:N57,'ONT to BND'!N56:N57)
C47	C47	=SUM('BND to ONT'!N59:N60,'ONT to BND'!N59:N60)
C48	C48	=SUM('BND to ONT'!N62:N63,'ONT to BND'!N62:N63)
C49	C49	=SUM('BND to ONT'!N65:N66,'ONT to BND'!N65:N66)
C50	C50	=SUM('BND to ONT'!N68:N69,'ONT to BND'!N68:N69)
C51	C51	=SUM('BND to ONT'!N71:N72,'ONT to BND'!N71:N72)
C52	C52	=SUM('BND to ONT'!N74:N75,'ONT to BND'!N74:N75)
C53	C53	=SUM('BND to ONT'!N77:N78,'ONT to BND'!N77:N78)
C54	C54	=SUM('BND to ONT'!N80:N81,'ONT to BND'!N80:N81)
C55	C55	=SUM('BND to ONT'!N83:N84,'ONT to BND'!N83:N84)
C56	C56	=SUM('BND to ONT'!N86:N87,'ONT to BND'!N86:N87)
C57	C57	=SUM('BND to ONT'!N89:N90,'ONT to BND'!N89:N90)
C58	C58	=SUM('BND to ONT'!N92:N93,'ONT to BND'!N92:N93)
C59	C59	=SUM(C28:C58)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C28:C58	Cell Value	<1	text	NO
B28:C58	Expression	=(TEXT($B28:$B58,"ddddddddd")=$E$35)	text	NO
B28:C58	Expression	=(TEXT($B28:$B58,"ddddddddd")=$E$34)	text	NO

I thought of a work-around using a nested IF statement which is currently being used in cells G29:G35. It works, but it gets a lot messier when trying to do the same for the month of February, as well as a February which falls on a leap year (29 days).
So....my question is, is there a neater way to make the existing formula {=AVERAGE(IF(WEEKDAY($B$28:$B$58,2)=MATCH(E15,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),$C$15:$C$45))} first check to see whether there are blank cells for B56, B57 and B58, and then decide whether or not to use them in the WEEKDAY function in the formula?

 

[etaf]

it will handle blank cells ?
but your cells are not blank as they have a formula in

its more to do with the way the dates are created

so weekday() even if the formula returns "" will still give a Value error -

which is what the problem is - looked into a couple of ways to solve - but so far no luck - i'm sure there is a simple fix