0

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 ?

4 Answers 4

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

1
  • 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, 2012 at 16:16
0

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.

1
  • Because if I ommit the date, the slope is not the same.
    – Oliver
    Feb 27, 2012 at 13:15
0

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .