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