Tanjima Hossain
This article will serve this purpose if you are looking for ways to calculate a daily average from hourly data in Excel. So, let’s get into the main article to know the easiest 3 ways of doing this task.
Download Workbook
3 Ways to Calculate Daily Average from Hourly Data in Excel
Here, we 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 we 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.
Method-1: Using AVERAGE, ROWS, and OFFSET Functions to Calculate Daily Average from Hourly Data in Excel
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.
Steps:
- Type the following formula in cell E4.
=AVERAGE(OFFSET(C$4,(ROWS(E$4:E4)-1)*24,0,24))Formula Breakdown
- ROWS(E$4:E4) → returns the number of range of rows from E$4:E4.
- Output → 1
- ROWS(E$4:E4)-1 → becomes
- 1-1 → 0
- (ROWS(E$4:E4)-1)*24 → becomes
- 0*24 → 0
- OFFSET(C$4,(ROWS(E$4:E4)-1)*24,0,24) → becomes
- OFFSET(C$4,0,0,24) → extracts a range from C$4 with a height of 24
- Output → $C$4:$C$27
- OFFSET(C$4,0,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)) → becomes
- AVERAGE($C$4:$C$27) → returns the average value of the views of the range $C$4:$C$27 for 24 hours of 9/20/2022.
- Output → 2900.791667
- AVERAGE($C$4:$C$27) → returns the average value of the views of the range $C$4:$C$27 for 24 hours of 9/20/2022.
- Press ENTER and drag down the Fill Handle.
Finally, you will get the daily averages of hourly views from 9/20/2022 to 9/24/2022.
Read More: How to Average a Column in Excel (7 Easy Methods)
Method-2: Using AVERAGE, IF, & INT Functions to Calculate Daily Average from Hourly Data in Excel
In this section, we are going to use the AVERAGE, IF, and INT functions to calculate the daily averages for views from 9/20/2022 to 9/24/2022.
Steps:
- Apply the following formula in cell E4.
=AVERAGE(IF(INT($B$4:$B$123)=D4,$C$4:$C$123))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 got TRUE for the corresponding rows.
Finally, the AVERAGE function will return the average value.
- Press ENTER and drag down the Fill Handle.
Eventually, you will get the daily averages of hourly views from 9/20/2022 to 9/24/2022.
Read More: How to Average Every Nth Row in Excel (3 Ways)
Similar Readings
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Calculate Average and Standard Deviation in Excel
- Calculate Average in Excel Excluding 0 (2 Methods)
- How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
- Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
Method-3: Implementing PivotTable to Calculate Daily Average from Hourly Data in Excel
Here, we are going to utilize the PivotTable option for having daily averages of hourly views from 9/20/2022 to 9/24/2022.
- Go to the Insert tab >> PivotTable.
Afterward, the PivotTable from the table or range dialog box will open up.
- Select the range as Table/Range.
- Click on the New Worksheet option and press OK.
After that, you will be taken to a new sheet that has two portions; PivotTable on the left side and PivotTable Fields on the right side.
- Drag down the Date & Hour field to the Rows area and the Hourly Views field to the Values
After that, 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.
In this way, the Custom Name will be changed to Average of Hourly Views.
- Right-click on any cell of the Row Labels indicated column.
- Choose the Group option among various options.
Then, the Grouping dialog box will appear.
- Check the boxes Starting at with the date 9/20/2022 and Ending at with the date 9/24/2022 and select the Days
- Press OK.
Finally, you will get the average hourly views for each day.
- To stop seeing the total amount, go to the PivotTable Analyze tab >> PivotTable group >> Options dropdown >> Options.
Later, the PivotTable Options wizard will appear.
- In the Total & Filters tab, uncheck the Show grand totals for rows and Show grand totals for columns
- Press OK.
In this way, we will get the following table with our average views per day.
Read More: How to Calculate Average Only for Cells with Values in Excel
Practice Section
For doing practice, we have added a Practice portion on each sheet on the right portion.
Conclusion
In this article, we tried to show the ways to calculate a daily average from hourly data in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
Related Articles
- How to Calculate Average of Multiple Columns in Excel (6 Methods)
- Calculate Average of Multiple Ranges in Excel (3 Methods)
- Get Average Time in Excel (3 Examples)
- How to Calculate 7 Day Moving Average in Excel (4 Ways)
- Calculate Moving Average for Dynamic Range in Excel (3 Examples)
- How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
