# Excel method to find the values closest to a given period

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?

-

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&lt;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))
```
-
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:

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

-