# Excel - Smooth a curve by calculating / interpolating source missing values

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 ?

-

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):

-
Yes, I cpuld do like this, but I would have to write another table. At this time, I just put an x on the lines I want to have their values on the graph, that fills a cell, letting the others empty. And the source takes of course all the column, including empty cells. –  Oliver Feb 27 '12 at 16:16

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.

-
Because if I ommit the date, the slope is not the same. –  Oliver Feb 27 '12 at 13:15

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.

-

It's possible.
I found this that is very helpful :