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.

 

Sample Dataset to Calculate Daily Average in Excel


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

How to Calculate Daily Average in Excel by Utilizing AVERAGE, ROWS and OFFSET Functions

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.


Method 2 – Using PivotTable to Calculate Daily Average

Steps:

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

Using PivotTable to Calculate Daily Average in Excel

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

PivotTable Fields

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

Read More: How to Calculate Sum & Average with Excel Formula


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

How to Calculate Daily Average in Excel by Combining AVERAGE, IF and INT Functions

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.

  • Press the Enter button and drag down the Fill Handle icon.

  • The daily averages of hourly views from 9/20/2022 to 9/24/2022 are returned.

Read More: How to Calculate Weekly Average in Excel


How to Calculate Average Daily Sales in Excel

 

Sample Dataset to Calculate Average Daily Sales in Excel

Steps:

  • Select Cell C5 and enter the following formula.

=AVERAGE(OFFSET(C$5,(ROWS(E$5:E5)-1)*24,0,24))

  • Use the AutoFill tool for the entire column.

  • Press the Enter key to get the daily average sales.

Read More:How to Calculate Monthly Average from Daily Data in Excel


Download Practice Workbook

 


Related Articles


<< Go Back to How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo