Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers 2

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.