How to Calculate Daily Average in Excel (3 Methods)

The sample dataset contains hourly views of different blog posts of ExcelDemy from 9/20/2022 to 9/24/2022. From these hourly views for a day, we will return the averages for this date.

Method 1 – Combining AVERAGE, ROWS, and OFFSET Functions to Calculate Daily Average in Excel

Steps:

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

Press the Enter key and drag the Fill Handle down.

The daily averages of hourly views from 9/20/2022 to 9/24/2022 have been returned.

Method 2 – Using PivotTable to Calculate Daily Average

Steps:

Select a cell from the data table.
Select PivotTable from the Insert tab.

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

Drag down the Date & Hour field to the Rows area and the Hourly Views field to the Values area.

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

The Value Field Settings wizard will open up.
Select the Average option.
Press OK.

Right-click on any cell of the Row Labels indicated column and choose the Group option.

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 the data is not grouped automatically.

The average daily views are returned as below.

Method 3 – Calculating Daily Average with Excel AVERAGE, IF, and INT Functions

Steps:

Enter 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 are matched with the date 9/20/2022 and for matching TRUE will appear, and for other non-matched values, FALSE will appear.

In the range, $C$4:$C$123 those hourly values will be returned that are TRUE in the corresponding rows.

The AVERAGE function will return the average value.