date – Daily and Hourly Averages from (m/d/yyyy h:mm) timestamps in Excel

date – Daily and Hourly Averages from (m/d/yyyy h:mm) timestamps in Excel

After clarification of the requirement for daily averages edited to cover both daily and hourly averages:

  1. Add a column (here B) for ‘H’ (ie hour) with =HOUR(A2) copied down.

(Note: Though formatted to show only m/d/y content of ColumnA is, in line with title, assumed to be all of mm/dd/yyyy hh:mm. Makes existing columns [with names jumbled] Month, Day, Year, Hour redundant).

  1. Select data range.
  2. Data, Subtotal, At each change in: TIMESTAMP, Use function: Average, Add subtotal to: check only columns G and to the right, OK.
  3. Uncheck Replace current subtotals in Subtotal and apply At each change in: H, Use function: Average, and Add subtotal to: as before, OK.
  4. Replace =SUBTOTAL(1, in Min column with =MIN( .
  5. Delete ‘spare’ Grand Average row.
  6. Reformat as required.

Hopefully this will be achieved and is what is required!:

SO13731807

Note midnight tonight is counted as within first hour of tomorrow.

I had a similar need and worked it out this way:

  1. Add a column for Date (assuming your dd/mm/yyyy hh:mm:ss data is in cell A2)

    =DATE(YEAR(A2),MONTH(A2),DAY(A2))
    
  2. Add a column for Year. If you have weeks from a single year, the year column can be neglected.

    =YEAR(A2)
    
  3. Add a column for Week Number

    =WEEKNUM(A2)
    
  4. Add 2 pivot tables, 1 for daily and 1 for weekly analysis.
  5. Choose fields Date and the quantities you want. Put Date in the Rows section and sum/average of values in the Values section. You will get a date wise sum/average of the values you need.
  6. In the weekly pivot table, do the same as above, just add Year and Week no in the Rows section instead of Dates as in above.
    Hope this helps

date – Daily and Hourly Averages from (m/d/yyyy h:mm) timestamps in Excel

Leave a Reply

Your email address will not be published.