Reputation: 111
How to calculate average sales per weekday using Google Sheets?
I have two columns, date and net sales. My goal is to get the average sales for Monday
I am trying the following formula:
=AVERAGEIF(WEEKDAY(A2:A),2,B2:B)
Explanation of attempt:
WEEKDAY(A2:A)is the range of dates calculated as week day numbers2is the day number condition (Monday)B2:Bis the range to sum
I tried moving the WEEKDAY function to the conditional, and it produced the same result:
=AVERAGEIF(A2:A,WEEKDAY(A2:A)=2,B2:B)
Finally, I tried using TEXT() instead of WEEKDAY():
=AVERAGEIF(TEXT(A2:A,"dddd"),"Monday",B2:B)
Nothing seems to work. All approaches result in a #DIV/0 divide by zero error :(
Here is my sample data. Note the date column has actual mm/dd/yyyy values but are displayed using <Day of the week>, <Month> <Day of Month> formatting:
| date | net sales |
|---|---|
| Saturday, February 1 | $963.27 |
| Sunday, February 2 | $1,331.65 |
| Monday, February 3 | $1,014.70 |
| Tuesday, February 4 | $956.89 |
| Wednesday, February 5 | $625.13 |
| Thursday, February 6 | $734.03 |
| Friday, February 7 | $832.71 |
| Saturday, February 8 | $1,534.83 |
| Sunday, February 9 | $974.43 |
| Monday, February 10 | $904.45 |
| Tuesday, February 11 | $746.86 |
| Wednesday, February 12 | $596.86 |
| Thursday, February 13 | $671.32 |
| Friday, February 14 | $880.46 |
| Saturday, February 15 | $1,197.67 |
| Sunday, February 16 | $1,350.67 |
| Monday, February 17 | $0.00 |
| Tuesday, February 18 | $525.15 |
| Wednesday, February 19 | $477.58 |
| Thursday, February 20 | $0.00 |
| Friday, February 21 | $0.00 |
| Saturday, February 22 | $0.00 |
| Sunday, February 23 | $0.00 |
| Monday, February 24 | $0.00 |
| Tuesday, February 25 | $0.00 |
| Wednesday, February 26 | $0.00 |
| Thursday, February 27 | $0.00 |
| Friday, February 28 | $0.00 |
Upvotes: 1
Views: 82
Answers (4)
Reputation: 34355
It's a quirk of Google Sheets that Sumif and Countif can be used with arrays (Sumif in the criteria part only) so there is another possibility
=ArrayFormula(sumif(weekday(A2:A),2,B2:B)/countif(weekday(A2:A),2))
| date | net sales | Monday Average |
|---|---|---|
| Saturday, February 1 | $963.27 | $479.79 |
| Sunday, February 2 | $1,331.65 | |
| Monday, February 3 | $1,014.70 | |
| Tuesday, February 4 | $956.89 | |
| Wednesday, February 5 | $625.13 | |
| Thursday, February 6 | $734.03 | |
| Friday, February 7 | $832.71 | |
| Saturday, February 8 | $1,534.83 | |
| Sunday, February 9 | $974.43 | |
| Monday, February 10 | $904.45 | |
| Tuesday, February 11 | $746.86 | |
| Wednesday, February 12 | $596.86 | |
| Thursday, February 13 | $671.32 | |
| Friday, February 14 | $880.46 | |
| Saturday, February 15 | $1,197.67 | |
| Sunday, February 16 | $1,350.67 | |
| Monday, February 17 | $0.00 | |
| Tuesday, February 18 | $525.15 | |
| Wednesday, February 19 | $477.58 | |
| Thursday, February 20 | $0.00 | |
| Friday, February 21 | $0.00 | |
| Saturday, February 22 | $0.00 | |
| Sunday, February 23 | $0.00 | |
| Monday, February 24 | $0.00 | |
| Tuesday, February 25 | $0.00 | |
| Wednesday, February 26 | $0.00 | |
| Thursday, February 27 | $0.00 | |
| Friday, February 28 | $0.00 |
Note
(1) This wouldn't work for Saturday because if weekday is applied to blank cells you get 7 = Saturday and the count is much too high. Adjusted formula
=ArrayFormula(sumif(weekday(A2:A),7,B2:B)/countifs(weekday(A2:A),7,A2:A,"<>"))
(2) As pointed out by @doubleunary, OP may want to exclude zeroes so the adjusted formula for Monday would be
=ArrayFormula(sumif(weekday(A2:A),2,B2:B)/countifs(weekday(A2:A),2,B2:B,"<>"&0))
Upvotes: 0
Reputation: 19145
=AVERAGEIF(WEEKDAY(A2:A),2,B2:B)
The issue is not with averageif() per se, but with the fact that you need to add an arrayformula() wrapper or similar to array enable weekday(), like this:
=averageif(index(weekday(A2:A)), 2, B2:B)
But that gets you the average for just one day of the week. To get the average for all days of the week in one go, use query(), like this:
=query(
A1:B,
"select dayOfWeek(Col1), avg(Col2)
group by dayOfWeek(Col1)",
1
)
The zeros at the bottom of the sample data table look like they signify data that doesn't exist yet. To leave out those rows, so that they don't distort the result, add a where clause, like this:
=query(
A1:B,
"select dayOfWeek(Col1), avg(Col2)
where Col2 > 0
group by dayOfWeek(Col1)",
1
)
In the event that there may be legitimate zeros in the data, but you still need to strip unwanted zeros at the bottom, use filter(), like this:
=query(
filter(
A1:B,
row(B1:B) <= max(filter(row(B1:B), B1:B <> 0))
),
"select dayOfWeek(Col1), avg(Col2)
group by dayOfWeek(Col1)",
1
)
1 stands for Sunday and 7 stands for Saturday.
See query().
| date | net sales | dayofweek(date) | avg net sales | |
|---|---|---|---|---|
| Saturday, February 1 | $963.27 | 1 | $1,218.92 | |
| Sunday, February 2 | $1,331.65 | 2 | $639.72 | |
| Monday, February 3 | $1,014.70 | 3 | $742.97 | |
| Tuesday, February 4 | $956.89 | 4 | $566.52 | |
| Wednesday, February 5 | $625.13 | 5 | $702.68 | |
| Thursday, February 6 | $734.03 | 6 | $856.59 | |
| Friday, February 7 | $832.71 | 7 | $1,231.92 | |
| Saturday, February 8 | $1,534.83 | |||
| Sunday, February 9 | $974.43 | |||
| Monday, February 10 | $904.45 | |||
| Tuesday, February 11 | $746.86 | |||
| ... | ... |
Upvotes: 1
Reputation: 37050
AVERAGEIF() or AVERAGEIFS() doesn't work with virtual range. It only works on pure cell range. You may try combination of few other functions like-
=AVERAGE(FILTER(B2:B29,TEXT(A2:A29,"dddd")="Monday"))
| date | net sales | Average (Result) | |
|---|---|---|---|
| Saturday, February 1 | $963.27 | 479.7875 | |
| Sunday, February 2 | $1,331.65 | ||
| Monday, February 3 | $1,014.70 | ||
| Tuesday, February 4 | $956.89 | ||
| Wednesday, February 5 | $625.13 | ||
| Thursday, February 6 | $734.03 | ||
| Friday, February 7 | $832.71 | ||
| Saturday, February 8 | $1,534.83 | ||
| Sunday, February 9 | $974.43 | ||
| Monday, February 10 | $904.45 | ||
| Tuesday, February 11 | $746.86 | ||
| Wednesday, February 12 | $596.86 | ||
| Thursday, February 13 | $671.32 | ||
| Friday, February 14 | $880.46 | ||
| Saturday, February 15 | $1,197.67 | ||
| Sunday, February 16 | $1,350.67 | ||
| Monday, February 17 | $0.00 | ||
| Tuesday, February 18 | $525.15 | ||
| Wednesday, February 19 | $477.58 | ||
| Thursday, February 20 | $0.00 | ||
| Friday, February 21 | $0.00 | ||
| Saturday, February 22 | $0.00 | ||
| Sunday, February 23 | $0.00 | ||
| Monday, February 24 | $0.00 | ||
| Tuesday, February 25 | $0.00 | ||
| Wednesday, February 26 | $0.00 | ||
| Thursday, February 27 | $0.00 | ||
| Friday, February 28 | $0.00 |
Upvotes: 1
Reputation: 44
Your AVERAGEIF formulas aren't working because WEEKDAY(A2:A) returns an array, which AVERAGEIF doesn't handle properly. To fix this, you can use a helper column to filter the Monday’s values correctly.
-- By using a helper column you can easily filter your week day name. You can refer my these steps for creating a helper column:
- Add a helper column to extract the weekday name:
=TEXT(A2, "dddd")
This will display "Monday", "Tuesday", etc., for each date.
- Use AVERAGEIF to find the average net sales for Mondays:
=AVERAGEIF(C2:C100, "Monday", B2:B100)
Here, C2:C100: to extract column containing weekday names.
"Monday": This to condition and filter only monday data.
B2:B100: Extract the column with net sales data.
This method ensures that AVERAGEIF works correctly without any array-related issues. Let me know if you need further clarification!
Upvotes: 1
Related Questions
- Count in specific ways in Google sheets
- Google Sheets - formula for multiple values from different columns are fulfilled
- In Google Sheets, how can you return the next Friday from today, excluding today if today is Friday?
- How to Average by day of week in Google Sheets?
- Google Sheets - Iterate through dates
- Automatic calculation based on date and tickbox state
- How to find a string is present in array of strings in Google Spreadsheet?
- FILTER non-working days from range/list of dates
- Averages for each row in my dataframe by a condition and put on a new dataframe