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.
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.
- At first, type the following formula in cell E4.
- 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.
- Finally, you will get the daily averages of hourly views from 9/20/2022 to 9/24/2022.
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.
- Initially, select a cell from the data table.
- Now, select PivotTable from the Insert tab.
- 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.
- 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.
- How to Ignore #N/A Error When Getting Average in Excel
- Calculate Average Days in Excel (2 Easy Ways)
- How to Calculate Average Numbers in Excel (9 Handy Methods)
- Calculate Average of Multiple Ranges in Excel (3 Methods)
- How to Calculate Average True Range in Excel (with Easy Steps)
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.
- Firstly, apply the following formula in cell E4.
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.
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.
However, follow the steps below to find the daily average sales of the company.
- Firstly, select Cell C5 and enter the following formula.
- Secondly, you can use the AutoFill tool for the entire column.
- Lastly, press the Enter key to get the daily average sales.
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.
- 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)
- Average a Column in Excel (7 Easy Methods)
- How to Calculate Average of Averages in Excel (with Easy Steps)
- Calculate Average Only for Cells with Values in Excel