How to Calculate Daily Average from Hourly Data in Excel

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

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

Get the daily averages of hourly views from 9/20/2022 to 9/24/2022.

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

We will 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))`

The INT function will return the integer values of the range \$B\$4:\$B\$123 by extracting only date values. The IF function will check if the values match the date 9/20/2022. For matching values, TRUE will appear, and for other non-matched values, FALSE will appear. Those hourly values will be returned among the range \$C\$4:\$C\$123, for which we have TRUE for the corresponding rows.
The AVERAGE function will return the average value.

• Press ENTER and drag down the Fill Handle.

Get the daily averages of hourly views from 9/20/2022 to 9/24/2022.

Method-3 – Implementing PivotTable to Calculate Daily Average from Hourly Data in Excel

Utilize the PivotTable option for daily average hourly views from 9/20/2022 to 9/24/2022.

• Go to the Insert tab >> PivotTable.

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.

You will be taken to a new sheet with two portions: a 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.

In the Values area, click on the dropdown symbol next to the field Sum of Hourly Views to select the Value Field Settings option.

The Value Field Settings wizard will open up.

• Select the Average option and press OK.

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.

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.

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.

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.

Get the following table with our average views per day.

Related Articles

<< Go Back to

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF