How to Calculate Daily Average in Excel (3 Easy Ways)

Microsoft Excel is a powerful program. 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 from daily data inputs. There are multiple ways available to carry out the operation. In this article, I will show you 3 easy ways to calculate the daily average in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


What Is Daily Average?

In general, 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. Moreover, the average value helps us to get an idea of the whole population instead of going through every input. An average can be calculated for various purposes on a daily basis. It is called the daily average. A general formula of the data can be as below.

For example, the daily average sales of a shop will be the following:

Daily Average Sales = Total Sales / Number of Items


3 Easy Ways to Calculate Daily Average in Excel

Finding the average value is essential. To illustrate, I will use a dataset as an example. Here, I have a large dataset containing hourly views of different blog posts of ExcelDemy from 9/20/2022 to 9/24/2022. Using these hourly views for a day, I will try to get the averages for this specific date with formulas or PivotTable. We have used Microsoft Excel 365 version for creating this article. However, you can use any other version at your convenience. For the purpose of demonstration, I have used the following dataset.

Sample Dataset to Calculate Daily Average in Excel


1. Utilizing AVERAGE, ROWS and OFFSET Functions

In this section, we will calculate the daily averages for views from 9/20/2022 to 9/24/2022 using the AVERAGE, OFFSET, and ROWS functions. However, follow the steps below in order to complete the operation easily.

📌 Steps:

  • At first, type the following formula in cell E4.

=AVERAGE(OFFSET(C$4,(ROWS(E$4:E4)-1)*24,0,24))

How to Calculate Daily Average in Excel by Utilizing AVERAGE, ROWS and OFFSET Functions

Formula Breakdown

  • ROWS(E$4:E4) returns the number of rows from E$4:E4.
  • ROWS(E$4:E4)-1 becomes 0.
  • (ROWS(E$4:E4)-1)*24 becomes 0.
  • OFFSET(C$4,(ROWS(E$4:E4)-1)*24,0,24) extracts a range from C$4 with a height of 24.
  • AVERAGE(OFFSET(C$4,(ROWS(E$4:E4)-1)*24,0,24)) returns final output 791667.
  • After that, press the Enter key and drag down the Fill Handle icon.

Read More: How to Find Average with OFFSET Function in Excel (3 Examples)


2. Using PivotTable to Calculate Daily Average

Here, I am going to utilize the PivotTable option to have daily averages from 9/20/2022 to 9/24/2022. Usually, PivotTable is a common tool in Excel. Hence, follow the steps properly in order to calculate the daily average of the data using PivotTable.

📌 Steps:

  • Initially, select a cell from the data table.
  • Now, select PivotTable from the Insert tab.

Using PivotTable to Calculate Daily Average in Excel

  • Click on the New Worksheet option from the dialog box and press OK.

  • Then, drag down the Date & Hour field to the Rows area and the Hourly Views field to the Values area.

PivotTable Fields

  • click on the dropdown symbol of the field Sum of Hourly Views in the Values area to select the Value Field Settings option.

  • Afterward, the Value Field Settings wizard will open up select the Average option, and press OK.

  • After that, right-click on any cell of the Row Labels indicated column and choose the Group option among various options.

  • From the Grouping dialog box, check the boxes Starting at with the date 9/20/2022 and Ending at with the date 9/24/2022, and select the Days if your data is not grouped automatically.

  • Finally, you will get the average daily views as in the below picture.

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


Similar Readings


3. Combining AVERAGE, IF and INT Functions

Fortunately, you can combine AVERAGE, IF and INT functions in order to find the daily average. In this part, I am going to use the AVERAGE, IF, and INT functions to calculate the daily averages for views from 9/20/2022 to 9/24/2022. However, you need to go through the following steps.

📌 Steps:

  • Firstly, apply the following formula in cell E4.

=AVERAGE(IF(INT($B$4:$B$123)=D4,$C$4:$C$123))

How to Calculate Daily Average in Excel by Combining AVERAGE, IF and INT Functions

Here, the INT function will return the integer values of the range $B$4:$B$123 by extracting only date values. Then, the IF function will check if the values are matched with the date 9/20/2022 and for matching TRUE will appear, and for other non-matched values FALSE will appear. Among the range, $C$4:$C$123 those hourly values will be returned for which we have TRUE for the corresponding rows. Finally, the AVERAGE function will return the average value.

  • Secondly, press the Enter button and drag down the Fill Handle icon.

  • Eventually, you will get the daily averages of hourly views from 9/20/2022 to 9/24/2022.

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


How to Calculate Average Daily Sales in Excel

However, Excel provides the AVERAGE function for calculating the average. You just need to select the respective cell range. So, learn the following steps to carry out the operation. For the purpose of demonstration, I have chosen the sales of a private company from 1/1/2022 to 1/10/2022.

Sample Dataset to Calculate Average Daily Sales in Excel

However, follow the steps below to find the daily average sales of the company.

📌 Steps:

  • Firstly, select Cell C5 and enter the following formula.

=AVERAGE(OFFSET(C$5,(ROWS(E$5:E5)-1)*24,0,24))

  • Secondly, you can use the AutoFill tool for the entire column.

  • Lastly, press the Enter key to get the daily average sales.

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


Conclusion

These are all the steps you can follow to calculate daily average in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit ExcelDemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo