Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 0 down vote accepted

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))
share|improve this answer
1  
I think formula in Step 5 should read =INDEX($B:$B,IF((A2-F1)<(G1-A2),E1+1,E1)) – pnuts Jan 23 '13 at 9:25
    
Thanks! It was actually the wrong reference - it should have been D1 instead of A1, i.e. figure out the delta between the full minute (D1) and the two closest matches. Fixed! – Peter Albert Jan 23 '13 at 10:22
    
I think something in Step 5 is still not quite correct? – pnuts Mar 13 '13 at 14:11

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:

SU540294 example

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .