2

I recently asked a question about linear interpolation of data for missing values. Here is the link to that question

What is the dynamic solution to a linear interpolation of data in a row with missing values with an indefinite number of missing value sequences?

A new problem has arisen that pertains to the case where the blank sequence ends in a blank, and not a filled value. In that case, the formula repeats the last filled value in those latest blanks. What i want to do is, have some way like conditional formatting or adding a 0.0000009 sort of number to distinguish it as stale data.

Below is the sample data:- (This data is the result of a formula that is described by the solution provided)

Date    Data    Updated Data
1/1/2001    1   1.000000000
2/1/2001    2   2.000000000
3/1/2001    3   3.000000000
4/1/2001    4   4.000000000
5/1/2001        4.250000090
6/1/2001        4.500000090
7/1/2001        4.750000090
8/1/2001    5   5.000000000
9/1/2001    6   6.000000000
10/1/2001   7   7.000000000
11/1/2001       7.666670090
12/1/2001       8.333330090
1/1/2002    9   9.000000000
2/1/2002    11  11.000000000
3/1/2002        11.000000090
4/1/2002        11.000000090

How can i distinguish between the last two 11s and the normal data up above? Since this data is stale and technically not the right data.?

Abhay Saini
  • 97
  • 7
  • So what should the updated data be? – Scott Craner Sep 13 '17 at 19:19
  • The last two 11s might be colored differently or maybe we can add a 0.000000001 at the end of the last 2 11s? You might have noticed that i already put the 0.00000009 at the end the calculated numbers, so i thought that the last 2 11s can have 0.000000091 instead of 0.00000009 (which is at the end of the calculated numbers) since the calculated numbers and the last 2 11s are different. – Abhay Saini Sep 13 '17 at 19:25
  • for the coloring just use a conditional formatting rule on Column C with the following rule: `=ROW()>=MATCH(1E+99,B:B)` and color it like you want. – Scott Craner Sep 13 '17 at 19:29
  • Thanks! I think that >= should >. Otherwise it colors the actual 11 as well (the third from the bottom) – Abhay Saini Sep 13 '17 at 19:33
  • Yup, good catch. – Scott Craner Sep 13 '17 at 19:38

0 Answers0