Average for time ranges (1 Viewer)

NSAMSA · Jun 30, 2017

Hi all:

I am having an issue on some data collection that I was hoping I could receive some assistance on.

I have a data collector that checks the temperature of a piece of machinery in my plant 4 times per second. The machinery goes in cycles which create a curve in temperature going down. In order to check the control of the procedure, I would like to find the average temperature per minute. The report I get has one column for time with a format of (6/28/17 10:20:04.000) and so on as the time changes. The next column is the temperature.

I have made a column with cells with minute intervals (6/28/17 10:20:00.000, 6/28/17 10:21:00.000, etc.)

I would like to make a formula that says to average all numbers in column B where column A is between 10:20 and 10:21 and so on and so forth. I've tried various forms of AverageIfs and AverageIf to no avail. Any help with this would be greatly appreciated.

Thank you

Ranman256 · Jun 30, 2017

I make a 'report' table. I post the records to this table. Where t here is an extra field,ELAPSE
I then run code to scant the table,get the 1st rec time,move to next rec,
Get that time and calc the elapsed time to put in ELAPSE field.
Repeat for every rec.

Then run a query to calc AVG(elapse)

Ranman256 · Jun 30, 2017

Code:

Public Sub LoopThruRst()
Dim rst
dim vStart ,vEnd, vElaps

vStart =  0
Set rst = CurrentDb.OpenRecordset("qsMyQuery")
With rst
   while not .eof
      vEnd = .fields("Time").value      
      vElaps = dateDiff("n",vStart, vEnd)
       
       .fields("Elapse").value = vElaps
       .update

       vStart = vEnd
      .MoveNext
   wend
End With

Set rst = Nothing
msgbox "done"
End Sub

Average for time ranges (1 Viewer)

NSAMSA

Registered User.

Ranman256

Well-known member

Ranman256

Well-known member

Similar threads

Users who are viewing this thread