We frequently use Excel to calculate a weekly average to keep track of our costs. When dealing with a small size of data, we can calculate a weekly average manually. However, with huge datasets, this can be a difficult operation. Fortunately, we have Excel. Excel is capable of handling these scenarios effectively. This article covers 4 simple methods so that we can calculate a weekly average using Excel quite easily.
How to Calculate Weekly Average in Excel: 4 Easy Ways
In this section of the article, we will learn 4 simple ways to calculate the weekly average in Excel. Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Calculating Weekly Average from Daily Data
Let’s say, we have the Daily Expense of Adam as our dataset. In the dataset, we have the amount Spent by Adam daily. Our goal is to calculate a weekly average from this dataset. To do this we will follow 2 approaches. The 1st one is by using the SUM function and in the 2nd method, we will use the AVERAGE function.
1.1 Applying SUM Function to Calculate Weekly Average
Applying the SUM function is one of the easiest ways to calculate the weekly average in Excel. The SUM function returns the sum of a selected range. Let’s follow the steps mentioned below.
Steps:
- Firstly, create a new column named Weekly Average as shown in the following image.
- Following that, enter the formula given below in cell E5.
=SUM(D5:D11)/7Here, the range D5:D11 refers to the first 7 cells of the Spent column.
- Then, press ENTER.
As a result, you will have the weekly average for the 1st week as marked in the following picture.
- Next, use the AutoFill feature of Excel to obtain the remaining outputs.
Read More: How to Calculate Daily Average in Excel
1.2 Employing AVERAGE Function
Employing the AVERAGE function is another smart way to calculate the weekly average in Excel. The AVERAGE function simply returns the average value of the selected cells of a worksheet. Let’s use the steps discussed in the following section to calculate a weekly average.
Steps:
- Firstly, create a new column named Weekly Average as shown in the image below.
- After that, insert the following formula in cell E5.
=AVERAGE(D5:D11)Here, the AVERAGE function will return the average value of the range D5:D11.
- Now, hit ENTER.
Consequently, you will have the weekly average for the 1st week as demonstrated in the following image.
- Now, by using the AutoFill option of Excel, you can have the rest of the outputs.
Read More: How to Calculate Monthly Average from Daily Data in Excel
2. Computing Weekly Average from Monthly Data
In this section of the article, we will calculate the weekly average in Excel from monthly data. Let’s say, we have the Monthly Expense of Nathan as a dataset. In the dataset, we have the amount Spent by Nathan for September. We aim to calculate the weekly average. We will learn 2 ways to do this. In the 1st method, we will use the AVERAGEIFS function and in the 2nd method, we will use the SUMIF and COUNTIF functions.
2.1 Using AVERAGEIFS Function to Calculate Weekly Average
Using the AVERAGEIFS function is one of the most efficient ways to calculate the weekly average in Excel. The AVERAGEIFS function returns the average value of a selected range based on one or multiple conditions. Let’s use the steps mentioned below to do this.
Steps:
- Firstly, create 2 new columns named Week Number and Weekly Average as shown in the following image.
- Following that, enter the following formula in cell D5.
=WEEKNUM(B5)Here, cell B5 represents the cell of the Date column. Now, the WEEKNUM function will return the number of the week in the year for a specific date.
- Then, press ENTER.
As a result, you will have the following output on your worksheet.
- After that, use the AutoFill option of Excel to get the rest of the Week Numbers as shown in the following image.
- Now, use the following formula in cell E5.
=IF(D6=D5,"",AVERAGEIFS($C$5:$C$19,$D$5:$D$19,D5))Here, cell D6 refers to the 2nd cell of the Week Number column, cell D5 indicates the 1st cell of the Week Number column, the range $C$5:$C$19 represents the cells of the Spent column, and the range $D$5:$D$19 indicates the cells of the Week Number column.
Formula Breakdown
- AVERAGEIFS($C$5:$C$19,$D$5:$D$19,D5) → This returns the average of a selected range based on one or more criteria.
- $C$5:$C$19 → It is the average_range argument.
- $D$5:$D$19 → This refers to the criteria_range1 argument.
- D5 → It represents the criteria1 argument.
- Output → 41.33
- IF(D6=D5,””,AVERAGEIFS($C$5:$C$19,$D$5:$D$19,D5)) → It becomes IF(D6=D5,””,41.33).
- The IF function returns a value if the specified condition is met and returns another value if the specified condition is not met. In this case, if the next cell (D6) becomes equal to the current cell (D5), that means there are more entries of the current Week Number. So if this condition is satisfied then keep the cell blank (“”). But when the next cell is not equal to the current cell that means the Week Number has changed. So, we will show the value from the AVERAGEIFS function.
- D6=D5 → It is the logical_test argument.
- “” → This refers to the [value_if_true] argument.
- 41.33 → It is the [value_if_false] argument.
- Output → Blank cell.
- Then, hit ENTER.
Consequently, you will see a blank output in cell E5 as it is not the last entry of the 36th week.
- Subsequently, use the AutoFill option of Excel to get the Weekly Average of all the weeks as shown in the image below.
You can add a fill to the output cells to make them stand out from others. To do this, let’s follow the steps given below.
- Firstly, go to the Home tab from Ribbon.
- After that, select the Fill Color option from the Font group.
- Then, choose your preferred color from the drop-down.
- Now, follow the same steps to apply the Fill Color in the remaining outputs.
Consequently, your final output table will look like the following picture.
Read More: How to Calculate Average of Multiple Columns in Excel
2.2 Utilizing SUMIF and COUNTIF Functions
Utilizing the SUMIF and COUNTIF functions is another effective way to calculate the weekly average in Excel. The SUMIF function returns the sum of a selected range based on 1 condition. The COUNTIF function simply counts the number of cells in a selected range that meets a certain condition. Now, let’s follow the procedure discussed in the following section.
Steps:
- Firstly, create 3 new columns named Week Number, Weekly Spent, and Weekly Average respectively.
- Following that, use the following formula in cell D5.
=WEEKNUM(B5)- Then, press ENTER.
As a result, you will have the Week Number for the Date in cell B5.
- Now, by using the AutoFill feature of Excel, you can get the rest of the outputs as shown in the image below.
- After that, use the formula given below in cell E5.
=SUMIF($D$5:$D$19,D5,$C$5:$C$19)Here, the cell D5 indicates the 1st cell of the Week Number column, the range $D$5:$D$19 refers to the cells of the Week Number column, and the range $C$5:$C$19 represents the cells of the Spent column.
Now, the SUMIF function will return the sum of the cells in the range $C$5:$C$19 based on the Week Number.
- Subsequently, hit ENTER.
As a result, you will have the Weekly Spent amount for Week Number 36 in cell E5.
- Next, you can use the AutoFill feature of Excel to have the Weekly Spent amount for all Week Numbers.
- At this stage, enter the following formula in cell F5.
=IF(D6=D5,"",E5/COUNTIF($D$5:$D$19,D5))Here, cell D6 refers to the 2nd cell of the Week Number column, and cell E5 represents the 1st cell of the Weekly Spent column.
Formula Breakdown
- COUNTIF($D$5:$D$19,D5) → This counts the cells in the range $D$5:$D$19 based on the Week Number.
- $D$5:$D$19 → It is the range argument.
- D5 → This refers to the criteria argument.
- Output → 3.
- IF(D6=D5,””,E5/COUNTIF($D$5:$D$19,D5)) → It becomes IF(D6=D5,””,E5/3).
- D6=D5 → It is the logical_test argument.
- “” → It represents the [value_if_true] argument.
- E5/3 → This refers to the [value_if_false] argument.
- Output → Blank cell.
- Then, press ENTER.
Subsequently, you will have a blank cell as output in cell F5 as it is not the last entry of the 36th week.
- Following that, use the AutoFill feature to get the weekly average for each week as demonstrated in the following picture.
- Finally, by following the steps from the previous method, you can apply Fill Color to the output cells.
Consequently, your final output table will look like the following image.
Read More: How to Get Average Time in Excel
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
That’s all about today’s session. I strongly believe that this article was able to guide you to calculate the weekly average in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.
Happy learning!
Related articles
- How to Calculate Average Rating in Excel
- How to Calculate 5 Star Rating Average in Excel
- How to Calculate Average Growth Rate in Excel
- How to Calculate Daily Average from Hourly Data in Excel
- How to Calculate Average Days in Excel
<< Go Back to Daily Weekly Monthly Average in Excel | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
