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.

**Table of Contents**hide

## 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**.

#### 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**.

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.

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

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

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

#### 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**.

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 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 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 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 be looking like the following picture.

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

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

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

## 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!