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
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
Maybe like this:
C4 and down: =MATCH(2, 1/ISNUMBER(B$3:B3)) confirmed with Ctrl+Shift+Enter
Please Login or Register to view this content.
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) ) )
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks