How do I average numbers based on day of week?
Hi everyone,
I keep track of a number of figures on a daily basis (sales, number of transactions, etc...). My spreadsheet is laid out with the columns for the dates and rows for the figures. For example, 7/1/15 is column C, 7/2/15 is column D, 7/3/15 is column E, etc... and Sales is row 2, Transactions is row 3, etc...
I want to find out the averages for each day of the week, e.g. the average sales done on Sundays. Right now, I'm using the average function with each individual cell entered). For example, =average(c2,c9,c16,c23,etc...). This is manageable for now, but I can see it getting unwieldy.
I've been trying to get a single formula for this, but I haven't been able to get anything to work. Basically "average this range if the dates in this range are Sundays." I've tried sumproduct, averageif, and a few others, and keep getting syntax errors. Unfortunately, it seems like spreadsheet skills aren't up to the task.
Any ideas? Thanks for your help.
-Joe
MacBook Pro, OS X Mavericks (10.9.5)