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)/7`

Here, 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 theargument.*average_range***$D$5:$D$19 →**This refers to theargument.*criteria_range1***D5 →**It represents theargument.*criteria1***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 theargument.*logical_test***“” →**This refers to theargument.*[value_if_true]***41.33 →**It is theargument.*[value_if_false]***Output → Blank cell**.

- The

- 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 theargument.*range***D5**→ This refers to theargument.*criteria***Output**→**3**.

**IF(D6=D5,””,E5/COUNTIF($D$5:$D$19,D5))**→ It becomes**IF(D6=D5,””,E5/3)**.**D6=D5**→ It is theargument.*logical_test***“”**→ It represents theargument.*[value_if_true]***E5/3**→ This refers to theargument.*[value_if_false]***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**