Excel method to find the values closest to a given period

Question

I have some data measured at non uniform time intervals, for example:

Time(s)     Value(KB)
  2.1         304
 43.0         400
 60.2         380
 87.0         440
 89.5         445
102.2         460
124.3         470

I have this in a CSV with the time increasing from T=0 as above, and I want to sample Value at a periodic interval (60 seconds) to match some other data that I have.

I was trying to use Microsoft Excel 2007 to accomplish the task of reducing the data to just the values that are closest to each 60 second interval (there is a bit of data so whether it is always higher (124.3), or lower (102.2), or absolute value closest (124.3), is not important). I have tried applying a formula to the Time column (MOD(Tn, 60)) but I cannot hit on a way to take this output (Time values cycling between 0 and 60) and filter out everything but the closest value to 60 in each cycle. Any suggestions?

Answer 1

do the following in another table:

1. Column D: put the values has 60, 120, 180, etc.
2. Column E: =MATCH(D1,$A:$A) - this will give you the last row of the where the value is smaller than column D
3. Column F: =INDEX($A:$A,E1) - this returns the seconds value that's lower
4. Column G: =INDEX($A:$A,E1+1) - this returns the seconds value that's higher
5. Column H: =INDEX($B:$B,IF(D1-F1<G1-D1,E1+1,E1)) - this will provide you with the value (KB) that's closest

Alternatively, just place this formula in column F:

=INDEX($B:$B,IF(D1-INDEX($A:$A,E1)<INDEX($A:$A,E1+1)-D1,E1+1,E1))

or use this massive formula in column G:

=INDEX($B:$B,MATCH(D1,$A:$A)+IF(D1-INDEX($A:$A,MATCH(D1,$A:$A))<INDEX($A:$A,MATCH(D1,$A:$A)+1)-D1,1,0))

Answer 2

Append minute intervals (in seconds) to ColumnA, ensuring latest time is after existing data, and sort A:B on Time(s) , Smallest to Largest with My data has headers. Filter ColumnB to select only blanks and insert:

=IF(A4-A3>A5-A4,B5,B3)

into first blank (here Row 4) and copy down, then delete last row:

Could relatively easily be extended to interpolate between values (which may be more accurate) rather than simply pick the nearest value.