• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time Linked Data Gathering

H

Haircut100

New Member
Dear expert


Could you please give me a steer as to the most appropriate function for my data gathering task.


I have a data set containing logged data every 10mins of every day for 1 month. In the left hand column is a combined date and time value eg. '09/01/2008 08:30' . In the next column is a logged data value eg. '0.44'


In September I therefore have 30 different data reading that relate to 08:30. I would like to gather all 30 corresponding data readings into 1 cell and find their average, and then repeat the process for 08:40, 08:50 etc.


The end result would be a column of 10min data averages for the month.


I note that the date time column is number formatted as 'general', if this has any bearing on the matter.


Hope I've explained this clearly...


Kind regards


Noel
 
I would setup a Helper Column which just contains the times

eg =A10-Int(A10) and format as a time


Then you could use a SumProduct or a Pivot Table to summarise
 
Hi Noel,


I would suggest adding a helper column that should just contain the time. It sounds form your post that the field is actually a text field. If this is the case you can use the RIGHT() formula to isolate the time element form the text. You will now have a list of times and values.


You can now use the AVERAGEIF() formula to calculate an average.
 
You must log in or register to reply here.
Back
Top