How to Calculate Weekly Average in Excel (4 Easy Ways)

We frequently use Excel to calculate weekly average to keep track of our costs. When dealing with a small size of data, we can calculate 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 weekly average using Excel quite easily.


Download Practice Workbook


4 Simple Methods to Calculate Weekly Average in Excel

In this section of the article, we will learn 4 simple ways to calculate 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 on daily basis. Our goal is to calculate 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.

how to calculate weekly average in excel


1.1 Applying SUM Function to Calculate Weekly Average

Applying the SUM function is one of the easiest ways to calculate 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)/7

Here, the range D5:D11 refers to the first 7 cells of the Spent column.

  • Then, press ENTER.

Applying SUM Function to Calculate Weekly Average in Excel

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.

Final output of method 1 to calculate weekly average in excel


1.2 Employing AVERAGE Function

Employing the AVERAGE function is another smart way to calculate 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 weekly average.

Steps:

  • Firstly, create a new column named Weekly Average as shown in the image below.

Employing AVERAGE Function to calculate weekly average in excel

  • 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.

Final output of method 2 to calculate weekly average in excel


2. Computing Weekly Average from Monthly Data

In this section of the article, we will calculate 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 the month of September. Our aim is 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.

Computing Weekly Average from Monthly Data in Excel


2.1 Using AVERAGEIFS Function to Calculate Weekly Average

Using the AVERAGEIFS function is one of the most efficient ways to calculate 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.

Using WEEKNUM Function to Calculate Weekly Average in Excel

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, the 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 satisfies 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.

Using AVERAGEIFS Function to Calculate Weekly Average in Excel

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.

Using Fill Color for Weekly Average in Excel

  • Now, follow the same steps to apply the Fill Color in the remaining outputs.

Consequently, your final output table will be looking like the following picture.

Final output of method 3 to calculate weekly average in excel


2.2 Utilizing SUMIF and COUNTIF Functions

Utilizing the SUMIF and COUNTIF functions is another effective way to calculate 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.

Utilizing SUMIF and COUNTIF Functions to calculate weekly average in excel

  • Following that, use the following formula in cell D5.
=WEEKNUM(B5)
  • Then, press ENTER.

Using WEEKNUM Function to calculate weekly average in excel

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.

Utilizing SUMIF and COUNTIF Functions to calculate weekly average in excel

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.

Utilizing SUMIF and COUNTIF Functions to calculate weekly average in excel

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.

Consequently, your final output table will look like the following image.

Final output of method 4 to calculate weekly average 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.

Practice Section to calculate weekly average in excel


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to calculate weekly average in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo