CODEWITHSUNDEEP
excelexcel-formula
neutronhammer
neutronhammer

Reputation: 145

Extract data for a particular time-interval from a set of continous readings

I'm looking for alternative methods to extracting data, wind speed to be precise, for a particular month in a particular year. I have readings at 10-minute intervals from 2004 to 2019.

I'd like to, for example, extract all values for say February 2008.

This is what it looks like

Timestamp           Speed (m/s)
01.01.2004 00:00    0
01.01.2004 00:10    8,11
01.01.2004 00:20    8,09
01.01.2004 00:30    8,27

What I currently do is, use a combination of MATCH, ADDRESS and INDIRECT to get at the Timestamps of the first and last reading for each month.

I'd then use these in an array function

WINDATEN - Is the sheet with all the readings

2008             Start                     End
                 01.02.2008 00:00          29.02.2008 23:50
=MATCH()         214850                    219025
=ADDRESS()      'WINDATEN'!$B$214850       'WINDATEN'!$B$219025 
=INDIRECT()      21,36                      21.87

I'd like to know if there are, perhaps, more elegant ways to achieve what I want? Instead of the whole ADDRESS and INDIRECT business.

Upvotes: 0

Views: 502

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

Suppose you wanted to get the average wind speed for a range of times in a particular day. You could still use Match to get the first and last rows, but then use the following method to get Index to produce an array of values:

=AVERAGE(INDEX(B:B,MATCH(C2,A:A,0)):INDEX(B:B,MATCH(D2,A:A,0)))

entered as an array formula using CtrlShiftEnter

enter image description here

Upvotes: 1

Related Questions