Averaging on specific day of the week YTD

[julesdr]

Ok, so I built a spreadsheet to track my running throughout the year and I'm trying to see what my averages are on each day of the week. Through an exhaustive search, I can only come up with the following formula

=AVERAGE(IF(WEEKDAY(A2:A366)=2,B2:B366))

Column A is the Date where Column B is Miles Ran

But the problem with this formula is that it grabs all the zeroes from the rest of the Mondays in the year and so my average miles ran on a Monday is 0. I have tried using a helper column to only grab currently dated cells with AVERAGEIFS, but I end up with #value ! returned...

I am also having the same problem with my monthly averages as I'd like to see my average miles per day in a given month but on a MTD basis...

Thanks in advance for the help!

 

[gfranco]

Hello
Try This:

=[COLOR=#333333]AVERAGE[/COLOR](if([COLOR=#333333]WEEKDAY[/COLOR](A2:A28)=2;if(B2:B28>0;B2:B28)))

Note: Must end with "CTRL + SHIFT + ENTER"

 

[steve the fish]

Do you want an average of just the days when you ran? Or are the days that you decided not to run to be included in the average?

 

[julesdr]

I would like days that I don’t run included for and accurate average, just not the zeroes on future days...

 

[steve the fish]

You could try:

=AVERAGE(IF(WEEKDAY(A2:A366)=2,IF(A2:A366<=TODAY(),B2:B366)))

 

[julesdr]

You could try:

=AVERAGE(IF(WEEKDAY(A2:A366)=2,IF(A2:A366<=TODAY(),B2:B366)))

Returns a value of 0 even though I ran 3.2 Miles on the only Monday so far this year so my avg should be 3.2?

 

[julesdr]

All right, I had to basically reverse Steve the fishs formula to:

{=AVERAGE(IF(A2:A366<=TODAY,IF(WEEKDAY(A2:A366)=2,B2:B366)))}

The way Steve originally wrote it was basically, "If the day is Tuesday, calculate the average of all cells in column B that are dated 1/1 to today." vs "In cells dated 1/1 to today, calculate the average on Tuesdays."

Thank you for the help guys!