Excel – Smooth a curve by calculating / interpolating source missing values

chartsmicrosoft excelsimulationworksheet-function

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 ?

Best Answer

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):enter image description here