**Microsoft Excel **is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default **Excel Functions** that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to find the average by month from daily data inputs. There are multiple ways available to carry out the operation. This article will show you **5 **easy ways to **Calculate Monthly Average **from **Daily Data **in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## 5 Easy Ways to Calculate Monthly Average from Daily Data in Excel

The **Average **is something that represents an entire data range. Mathematically we can calculate this value by dividing the sum of the total data range by the total number of inputs. It’s called **Mean **in statistics. The average value helps us to get an idea of the whole population instead of going through every input. So, finding the average value is essential. To illustrate, we’ll use a dataset as an example. For instance, the following dataset contains **Date **input and **Net Sales**. There are **6 **months’ data. We’ll show the methods to **Calculate Monthly Average **from this data in **Excel**.

### 1. Calculate Monthly Average from Daily Data with SUM Function in Excel

In our first method, we’ll use **the SUM function**. This function computes the sum of a range. We’ll manually select the monthly range by looking up the date input. Then, divide the function output by the number of inputs in each month. Therefore, follow the steps below to perform the task.

**STEPS:**

- Before starting the main calculation, we’ll input the name of the months first.
- For this purpose, select the range
**E4:E9**. - Here, type the first day of each month respectively in each cell.
- See the below picture to understand better.

- Then, select the range again and press the
**Ctrl**and**1**keys together. - As a result, the
**Format Cells**dialog box will pop out. - Under the
**Number**tab, choose**Category > Custom**and**Type > mmmm**. - After that, press
**OK**.

- Thus, it’ll return the accurate name of the months.

- Now, select cell
**F4**. - Next, type the formula:

`=SUM(C5:C8)/4`

- Press
**Enter**. - Hence, you’ll get the average net sales for
**January**. - We divide it by
**4**as**January**month has**4 inputs.**

- In this approach, you can’t apply
**AutoFill**as the data inputs don’t maintain a pattern. - You’ve to type the formulas manually.
- Similarly, you’ll see the formula in
**F9**for the month of**June**is:

`=SUM(C18:C20)/3`

- In this way, you can easily get the monthly average from daily data.

**Read More:** **How to Calculate Sum & Average with Excel Formula**

### 2. Insert AVERAGE Function for Computing Daily Data Average by Month

However, excel provides **the AVERAGE function** for calculating the average. We just need to select the respective cell range. So, learn the following steps to carry out the operation.

**STEPS:**

- Firstly, choose cell
**F4**. - Here, input the formula:

`=AVERAGE(C5:C8)`

- Subsequently, click
**Enter**. - It’ll return the average of
**January**. - Again, you can’t use
**AutoFill here.** - You’ve to select the average range by checking the date inputs per month.

**Read More:** **How to Calculate Daily Average from Hourly Data in Excel**

**Similar Readings**

**How to Calculate Average from Different Sheets in Excel****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****How to Find Average of Specific Cells in Excel (3 Handy Ways)****Create Average Daily Balance Calculator in Excel (2 Easy Methods)****How to Calculate Average of Averages in Excel (with Easy Steps)**

### 3. Use AVERAGEIF Function to Determine Monthly Average in Excel

Moreover, **the AVERAGEIF function** can find the average from a range based on condition. To facilitate the use of this function, we’ll insert **the MONTH function** to extract the month only from the data range. Hence, follow the process to use **AVERAGEIF **to determine the monthly average in excel.

**STEPS:**

- First of all, click cell
**E4**. - Next, insert the formula:

`=MONTH(B5:B20)`

- Hit
**Enter**. - Thus, it’ll spill the month numbers.
- Look at the figure below.

- However, we will need the unique month numbers only instead of a single month appearing multiple times.
**The UNIQUE function**gives out distinct cell values from a range.- In this regard, select cell
**F5**. - Type the formula:

`=UNIQUE(E4:E19)`

- Hence, it’ll spill the distinct month numbers.

- Now, choose cell
**C22**. - Input the formula:

`=AVERAGEIF($E$4:$E$19,F5,$C$5:$C$20)`

- Here,
**$E$4:$E$19**is the criteria range,**F5**is the desired condition, and**$C$5:$C$20**is the sales range from where we’ll find the average. - Consequently, press
**Enter**. - Use
**AutoFill**to get other outputs. - In this way, you’ll easily obtain the monthly average values from daily data.

**Read More: ****How to Calculate Average of Multiple Ranges in Excel (3 Methods)**

### 4. Get Daily Data Average by Month Through AVERAGEIFS Function

We had to use multiple functions in different steps in the previous method. Some may find that a hassle. In this method, we’ll show how you can skip multiple steps and still get the required average value. For this purpose, we’ll apply **the AVERAGEIFS function**. This function **calculates the average** based on multiple conditions. Moreover, we’ll use **the EOMONTH function** in the argument. This function extracts the last day of a month. Therefore, learn the process.

**STEPS:**

- First, select cell
**F4**. - Here, type the formula:

`=AVERAGEIFS($C$5:$C$20,$B$5:$B$20,">="&E4,$B$5:$B$20,"<="&EOMONTH(E4,0))`

- Then, press
**Enter**. - Finally, apply
**AutoFill**. - As a result, you’ll get the monthly average values.

**NOTE: $C$5:$C$20**is the average range. The condition range is

**$B$5:$B$20**for both cases.

**“>=”&E4**is the first condition. It looks for values that are greater than the first day of

**January**. The second condition is

**“<=”&EOMONTH(E4,0)**which looks for data lower than the last day of

**January**.

**Read More:** **How to Calculate Daily Average in Excel (3 Easy Ways)**

### 5. Apply Excel Pivot Table to Calculate Monthly Average

Lastly, we’ll make use of the **Excel Pivot Table**. This feature is used when we have to automatically determine various calculations. It’s easier to get the values in the pivot table. Now, go through the steps.

**STEPS:**

- In the beginning, select the range
**B4:C20**. - Then, go to
**Insert**>**PivotTable**.

- Consequently, a dialog box will appear.
- Press
**OK**.

- Now, drag the
**Date**and the**Months**fields and drop them under the**Rows section.** - Next, drag the
**Net Sales**field and place it under the**Values**.

- Subsequently, choose
**Value Field settings**from the**Sum of Net Sales**drop-down. - The dialog box will emerge.
- Click
**Average**from the**Summarize Values By options.** - Hit
**OK**.

- Thus, a new worksheet will appear containing the monthly average net sales.

**Read More:** **How to Average Every Nth Row in Excel (3 Ways)**

## Conclusion

Henceforth, you will be able to **Calculate Monthly Average** from **Daily Data** in **Excel **using the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**How to Get Average Time in Excel (3 Examples)****Calculate Average Only for Cells with Values in Excel****How to Average a Column in Excel (7 Easy Methods)****Average Values Greater Than Zero in Excel (4 Ways)****How to Ignore #N/A Error When Getting Average in Excel****Calculate Weekly Average in Excel (4 Easy Ways)****How to Fix Divide by Zero Error for Average Calculation in Excel**