Table of Contents
To calculate the average by day of the week in Excel, you can use the AVERAGEIF function. This function allows you to specify a criteria and a range of cells to calculate the average of, based on that criteria. In this case, you would use the days of the week as the criteria and the values you want to average as the range. This will give you the average for each day of the week. Alternatively, you can use the PivotTable feature to group your data by day of the week and then calculate the average for each group. Both methods provide an efficient way to calculate the average by day of the week in Excel.
Calculate Average by Day of Week in Excel
Often you may want to calculate the average value grouped by day of week in Excel.
For example, suppose we have the following dataset and we’d like to calculate the average sales based on day of the week:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Extract the Day of Week from Dates
Next, type the following formula into cell C2 to extract the day of the week for the date in cell A2:
=TEXT(A2, "dddd")
We can then click and drag this formula down to each remaining cell in column D:
Step 3: Create List of Days of the Week
Next, create a list of the days of the week in column E:
Step 4: Calculate the Average by Day of Week
Next, type the following formula into cell F2 to calculate the average sales value based on day of the week
=AVERAGEIF($C$2:$C$15, E2, $B$2:$B$15)
Click and drag this formula down to each remaining cell in column F:
This tells us:
- The average sales on Mondays were 11.5.
- The average sales on Tuesdays were 30.
- The average sales on Wednesdays were 12.
- The average sales on Thursdays were 15.
And so on.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I calculate the average by day of the week in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-by-day-of-the-week-in-excel/
stats writer. "How can I calculate the average by day of the week in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-by-day-of-the-week-in-excel/.
stats writer. "How can I calculate the average by day of the week in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-by-day-of-the-week-in-excel/.
stats writer (2024) 'How can I calculate the average by day of the week in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-by-day-of-the-week-in-excel/.
[1] stats writer, "How can I calculate the average by day of the week in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I calculate the average by day of the week in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.