How to Calculate Daily Average from Hourly Data in Excel

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.

excel daily average from hourly dataexcel daily average from hourly data

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.

Using AVERAGE, ROWS, and OFFSET Functions to Calculate Daily Average from Hourly Data in Excel

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

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

result of method-1

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.

Using AVERAGE, IF, & INT Functions to Calculate Daily Average from Hourly Data in Excel

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.

result of Method-2

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


Similar Readings


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.

Implementing PivotTable to Calculate Daily Average from Hourly Data in Excel

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

PivotTable from the table or range dialog box to calculate a daily average from hourly data in Excel

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

drag fields

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.

select average option

  • Right-click on any cell of the Row Labels indicated column.

  • Choose the Group option among various options.

choose group option to calculate a daily average from hourly data in Excel

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.

result after calculating a daily average from hourly data in Excel

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.

practice section to calculate a daily average from hourly data in Excel


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo