Calculate average of 6 rows

[MelanieB]

One of my coworkers has a spreadsheet that lists readings taken 6 times every hour, every day, 7 days a week for several months.
He wants to get a list in Col C of the hourly average for each hour for every day.
Is there a formula or would I need a macro? I don't have a clue how to start this.

I've attached a screen shot.

Thank you!

[HansV]

You can easily do that with a pivot table. For this to work, there must be field names (column headings) above the first row of data - you cna insert a row if necessary.

Click anywhere in the table, then create a pivot table (Data |PivotTable and PivotChart Report in Excel 2003 or before, PivotTable on the Insert tab of the ribbon in Excel 2007 or later).
Add the date/time field to the row fields area, and the readings field to the data values area.
Double-click the data field, select Field Settings and change the summary function to Average.
When the pivot table has been created, right-click any of the date/time values and select Group. Specify that you want to group by Days and by Hours.

x393.png

[MelanieB]

That's wonderful, Hans. slap myself on forehead for not thinking pivot table.

But... one hitch.. his date/time format.
It's actually coming in as text field. I need to conver it to a date/time field. I tried doing a custom number format, which worked. But then it's still not in the right order to allow me to group it in the pivot. It is dd/mm/yy right now.

What's the fastest, easiest way to change it from his current text format (dd.mm.yy hh:mm:ss) to the correct date format (dd/mm/yy hh:mm:ss)?
He gave me over 100 worksheets like this so I need something fairly easy. I tried Left and Right and then copy/ paste special values and data text to columns to split it all out to separate cells and then concatenating it back to one in the right order.. gack. I know this is not the easiest or right way to do it.

[HansV]

Try this on a copy of the workbook:
- Select column A.
- Press Ctrl+H to activate the Replace dialog.
- Enter . in the Find what box en / in the Replace with box.
- Click Replace All.

Or does that cause problems with US date format?

[MelanieB]

Yeah, I thought of that, but it is still backwards from US date format. The main concern is that it needs to be in mm/dd/yy format. Hence my overly complicated attempts.

[HansV]

See if this macro does what you want:

Code: Select all

Sub FixDates()
  Dim r As Long
  Dim m As Long
  Dim strValue As String
  m = Cells(Rows.Count, 1).End(xlUp).Row
  Application.ScreenUpdating = False
  For r = 2 To m
    strValue = Cells(r, 1).Value
    strValue = Mid(strValue, 4, 2) & "/" & Left(strValue, 2) & "/" & Mid(strValue, 7)
    Cells(r, 1).Value = CDate(strValue)
  Next r
  Application.ScreenUpdating = True
End Sub

[MelanieB]

Ah HA! That's it! Thank youuuuuu