Smooth a curve by calculating / interpolating source missing values

Question

I have a table of values like this one :

18
18
18
46
46
46
46
46
57
57
57
57
57
57
69
69
69
69
69
69
64
64
64
64
64
64
56
56
56
48
48
48
44

or if you prefer :

18
0
0
46
0
0
0
0
57
0
0
0
0
0
69
0
0
0
0
0
64
0
0
0
0
0
56
0
0
48
0
0
44

They come from a table in which, for each day there is a value, and for some days there are not (in which case I can take 0 or the previous value for convenience).

What I would like is to display the evolution of the number with a curve. But for days where there are no values, the curves "goes down", even (of course) if I hard put 0 in the cell, and if I use the previous value, then the curve looks good but with (of course) unwanted plane steps.

What formula could I write into each cell to make it use the actual value if there is one, or to estimate the projected value for the day to achieve getting a smooth and continuous curve ?

Answer 1

Use the x,y data in a X Y (Scatter) plot. Use the # of days or the date as the X value and the value for the Y value. So the data looks like:

1   18
4   46
9   57
15  69
21  64
27  56
30  48
33  44

and the plot looks like (Scatter with smooth lines and markers):

Answer 2

If you have no other data to display for that date, why not simply omit that date? There is no accurate way to 'interpolate' a value between dates without knowing the differential of the curves function.

Answer 3

Expanding deed02392 answer:

You can mark each significant data point with an index corresponding to the day, for example: in this way, you can keep track of the days between the values without filling with 0s.

Then, I would suggest you to create a scatter plot with the data you have, making sure that each point is correctly placed according to the index.

Last thing, you can add a trendline of the order you want, being it linear, or exponential, or polynomial of an arbitrary order.

Answer 4

It's possible.
I found this that is very helpful :
http://www.microwaves101.com/downloads/Linterp_101_Rev1.1.xls

And for my particular purtpose, putting NA() instead of 0 or empty where I have to values make the curve ok, not pointing down each time a value is missing.