Interpolating between dates

**Wa_Wa** · 10-30-2009, 10:27 AM

I have a data set with dates in one columns and a price in the second colum corresponding to that date.

Not every date has a price, and the missing prices are not constant (ie no consistency in the number of days without a price)

Is there an easy formula to be able to interpolate the numbers?

Date______________Price
01/01/01__________100
01/02/01__________???
01/03/01__________???
01/04/01__________105
01/05/01__________???
01/06/01__________???
01/07/01__________???
01/08/01__________99

**Wa_Wa** · 10-30-2009, 02:43 PM

would anyone konw of a easier solution than simply taking the diff and dividing by the missing day count (not easy the the missing day count varies

**shg** · 10-30-2009, 04:11 PM

Maybe like this:

       ---A---- --B-- ---C---- ---D---- --E---
   3     Date   Price Helper 1 Helper 2 Interp
   4   01/01/01   100 #DIV/0!     5     100.00
   5   01/02/01   ???    2        5     101.67
   6   01/03/01   ???    2        5     103.33
   7   01/04/01   105    2        9     105.00
   8   01/05/01   ???    5        9     103.50
   9   01/06/01   ???    5        9     102.00
  10   01/07/01   ???    5        9     100.50
  11   01/08/01    99    5       #N/A    99.00

C4 and down: =MATCH(2, 1/ISNUMBER(B$3:B3)) confirmed with Ctrl+Shift+Enter

D4 and down: =MATCH(TRUE, ISNUMBER(B5:B$16), 0) + ROWS(D$3:D4) confirmed with Ctrl+Shift+Enter

E4 and down: =IF(ISNUMBER(B4), B4, INDEX(E$3:E3, C4) + (INDEX(E$3:E16, D4) - INDEX(E$3:E3, C4) ) * (ROWS(INDEX(E$3:E4, C4):E4) - 1) / (INDEX(A$3:A16, D4) - INDEX(A$3:A16, C4) ) )