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]

not yet found a simple solution - sum & count seems to have the same issue as does sumproduct
then its about working out the days in the month - simple to do with =DAY(EOMONTH(B10,0)) works for leap-years and then use indirect to change the range based on that number - but thats a volatile formula and can cause further issues , and seems very complicated for a simple issue - maybe using VBA , not my area

i'll have to see if i can find an alternative , to the problem the formula working out the date is causing ....

 

[FormR]

Hi, you could try like this which shouldn't error when encountering formula produced blanks.

=AVERAGE(IF(TEXT($B$28:$B$58,"dddd")=E29,$C$28:$C$58))

Note - it still needs to be array committed on your version of Excel.

 

[iclancy]

Ok, I get it. Looking for a solution as well

 

[iclancy]

Hi, you could try like this which shouldn't error when encountering formula produced blanks.

=AVERAGE(IF(TEXT($B$28:$B$58,"dddd")=E29,$C$28:$C$58))

Note - it still needs to be array committed on your version of Excel.

Oh, great! That's even better and is much neater than my fix Thanks again to both of you

 

[iclancy]

Hi,

Hoping to revisit this. I want to introduce a formula to the "Ridership by Day" table this formula pulls from that effectively removes that day from the average calculations found in the "Ridership by Day Summary" table if we cancel service for that day. Put another way, a canceled Monday would result in 3 Mondays being used in the calculation instead of 4. One thing I've tried is having the formula in "Ridership by Day" display a blank ("") whenever that day is canceled. This is now causing an issue with my SUMPRODUCT formulas in the "Ridership by Day Summary" table, causing them to produce VALUE errors. Not sure why this wasn't happening before, but it's the same issue that was occurring with the Average IF formula above (I think!).

Eastern Ridership Report Template-v3.4 - TEST.xlsx
	B	C	D
25	Ridership by Day Summary
26	Day of Week	Total/Day	Average/Day
27	Monday	#VALUE!	0.0
28	Tuesday	#VALUE!	0.0
29	Wednesday	#VALUE!	0.0
30	Thursday	#VALUE!	0.0
31	Friday	#VALUE!	0.0
32	Saturday	#VALUE!	0.0
33	Sunday	#VALUE!	0.0
34
35
36	Ridership by Day
37	8/1/23
38	8/2/23	0
39	8/3/23	0
40	8/4/23	0
41	8/5/23	0
42	8/6/23	0
43	8/7/23	0
44	8/8/23	0
45	8/9/23	0
46	8/10/23	0
47	8/11/23	0
48	8/12/23	0
49	8/13/23	0
50	8/14/23	0
51	8/15/23	0
52	8/16/23	0
53	8/17/23	0
54	8/18/23	0
55	8/19/23	0
56	8/20/23	0
57	8/21/23	0
58	8/22/23	0
59	8/23/23	0
60	8/24/23	0
61	8/25/23	0
62	8/26/23	0
63	8/27/23	0
64	8/28/23	0
65	8/29/23	0
66	8/30/23	0
67	8/31/23	0
68		0
Summary

Cell Formulas
Range	Formula
C27:C33	C27	=SUMPRODUCT((TEXT(B$37:B$67, "ddddddddd")=B27)*((C$37:C$67)))
D27:D33	D27	=AVERAGE(IF(TEXT($B$37:$B$67,"dddd")=B27,$C$37:$C$67))
B37	B37	=B10
B38:B65	B38	=IF(MONTH(B37+1)=MONTH($B$10),B37+1,(""))
B66	B66	=IF(MONTH(B64+2)=MONTH($B$10),B64+2,(""))
B67	B67	=IF(MONTH(B64+3)=MONTH($B$10),B64+3,(""))
C37	C37	=IF((AND('BND to ONT'!B2=TRUE,'ONT to BND'!B2=TRUE)),"",(SUM('BND to ONT'!N2:N3,'ONT to BND'!N2:N3)))
C38	C38	=SUM('BND to ONT'!N5:N6,'ONT to BND'!N5:N6)
C39	C39	=SUM('BND to ONT'!N8:N9,'ONT to BND'!N8:N9)
C40	C40	=SUM('BND to ONT'!N11:N12,'ONT to BND'!N11:N12)
C41	C41	=SUM('BND to ONT'!N14:N15,'ONT to BND'!N14:N15)
C42	C42	=SUM('BND to ONT'!N17:N18,'ONT to BND'!N17:N18)
C43	C43	=SUM('BND to ONT'!N20:N21,'ONT to BND'!N20:N21)
C44	C44	=SUM('BND to ONT'!N23:N24,'ONT to BND'!N23:N24)
C45	C45	=SUM('BND to ONT'!N26:N27,'ONT to BND'!N26:N27)
C46	C46	=SUM('BND to ONT'!N29:N30,'ONT to BND'!N29:N30)
C47	C47	=SUM('BND to ONT'!N32:N33,'ONT to BND'!N32:N33)
C48	C48	=SUM('BND to ONT'!N35:N36,'ONT to BND'!N35:N36)
C49	C49	=SUM('BND to ONT'!N38:N39,'ONT to BND'!N38:N39)
C50	C50	=SUM('BND to ONT'!N41:N42,'ONT to BND'!N41:N42)
C51	C51	=SUM('BND to ONT'!N44:N45,'ONT to BND'!N44:N45)
C52	C52	=SUM('BND to ONT'!N47:N48,'ONT to BND'!N47:N48)
C53	C53	=SUM('BND to ONT'!N50:N51,'ONT to BND'!N50:N51)
C54	C54	=SUM('BND to ONT'!N53:N54,'ONT to BND'!N53:N54)
C55	C55	=SUM('BND to ONT'!N56:N57,'ONT to BND'!N56:N57)
C56	C56	=SUM('BND to ONT'!N59:N60,'ONT to BND'!N59:N60)
C57	C57	=SUM('BND to ONT'!N62:N63,'ONT to BND'!N62:N63)
C58	C58	=SUM('BND to ONT'!N65:N66,'ONT to BND'!N65:N66)
C59	C59	=SUM('BND to ONT'!N68:N69,'ONT to BND'!N68:N69)
C60	C60	=SUM('BND to ONT'!N71:N72,'ONT to BND'!N71:N72)
C61	C61	=SUM('BND to ONT'!N74:N75,'ONT to BND'!N74:N75)
C62	C62	=SUM('BND to ONT'!N77:N78,'ONT to BND'!N77:N78)
C63	C63	=SUM('BND to ONT'!N80:N81,'ONT to BND'!N80:N81)
C64	C64	=SUM('BND to ONT'!N83:N84,'ONT to BND'!N83:N84)
C65	C65	=SUM('BND to ONT'!N86:N87,'ONT to BND'!N86:N87)
C66	C66	=SUM('BND to ONT'!N89:N90,'ONT to BND'!N89:N90)
C67	C67	=SUM('BND to ONT'!N92:N93,'ONT to BND'!N92:N93)
C68	C68	=SUM(C37:C67)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C37:C67	Cell Value	<1	text	NO

Let me know if this makes sense, if not I'm happy to answer any questions.

Thanks!

 

[FormR]

Hi, you could try changing your SUMPRODUCT() formula to this:

=SUMPRODUCT(--(TEXT(B$37:B$67,"dddd")=B27),C$37:C$67)

 

[iclancy]

Hi, you could try changing your SUMPRODUCT() formula to this:

=SUMPRODUCT(--(TEXT(B$37:B$67,"dddd")=B27),C$37:C$67)

Wow, a very simple solution. It looks like it's working. Thank you again so much! I don't recall why I had the TEXT function set to look for "ddddddddd" instead of "dddd". Can you please tell me what purpose the "--" serves before TEXT?

 

[FormR]

what purpose the "--" serves before TEXT?

Hi, It performs a mathematical operation on the TRUE/FALSE values returned by the logical test (TEXT(B$37:B$67,"dddd")=B27) which coerces the FALSE values to a 0 and the TRUE values to a 1

 

[iclancy]

Interesting, I will add this to my notes. Thanks