Average 24 hours of data when sample rate fluctuates

[David4321]

I have an Excel spreadsheet with imported data from an AWOS weather station. Column A is the date and time (in standard Excel Date/Time format). Columns B, C, etc are temperature, dew point, etc. There are typically about 3 data samples per hour but this sampling rate is not fixed -- it can vary due to missing reports or due to increased reporting frequency because of rapidly changing weather conditions. I want to make a column that is a moving average of the last 24 hours of temperature data. I have done so using the following array formula,

{=AVERAGE(IF(A12685:A12856>(A12856-1),B12685:B12856, FALSE))}

It works, but it seems clumsy and it is computationally expensive. Indeed, as the spreadsheet has grown quite large there is now a noticeable delay upon opening. I recently had two such spreadsheets open at the same time and things really bogged down.

There must be a better way. The following will give me the value of the temperature that is 24 hours before the current time stamp (note: current time stamp in this example is at A12856).

=VLOOKUP(A12856-1,A15:B12856,2)

But what I need is the row number in which that temperature resides and some way to use that row number in an AVERAGE() function to average all temperatures from that row to the current row.

Again, I’m seeking to replace the present array formula with a method that is less computationally expensive.

Thanks for any help.

[zbor]

can you upload example?

[David4321]

OK here's a redacted version with a lot of extraneous data removed to get it under the 1MB upload limit.

Whoops, I can't upload it for some reason. I get the following message:

"Database error
The Excel Help Forum database has encountered a problem."

I clicked on the "paper clip" symbol, browsed to the file on my desktop, selected "open" and then "upload." That's when I got the above error.

[zbor]

Maybe is too big... Try with smaller example (can't exceed 1MB).

[DonkeyOte]

Defining the rows via MATCHes and using a single AVERAGE wrapper will certainly be more efficient, as would a standard SUMIF/COUNTIF

Please Login or Register  to view this content.

edit: modified ranges per VLOOKUP rather than Array (ie 15:12856)

[David4321]

I already trimmed it down to 886 KB. OK, now I trimmed it down to 434KB.

[David4321]

Defining the rows via MATCHes and using a single AVERAGE wrapper will certainly be more efficient, as would a standard SUMIF/COUNTIF

Please Login or Register  to view this content.

Yes indeed, DonkeyOte, the code snippet looks good and the MATCH suggestion looks promising as well (I had to go into Excel help and look up "MATCH" to see what it does).

I'm sure with this info I'll be able to improve the efficiency of the computations.

Thanks,

[DonkeyOte]

If as it seems you intend to repeat calculation for each row then for sake of efficiency I would suggest:

Please Login or Register  to view this content.

Calculation should prove virtually instantaneous

The SUMIF/COUNTIF will be fast also but the above should prove quicker still.

[David4321]

Thanks DonkeyOte. I'll have to wait until tomorrow to make the suggested changes but I'm sure it will work out well. If there are any snags I'll probably be able to work them through myself. Meantime, I'll mark this thread solved.

Thanks again, DonkeyOte and all