Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 ?

share|improve this question

4 Answers 4

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

share|improve this answer
    
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.

share|improve this answer
    
Because if I ommit the date, the slope is not the same. –  Oliver Feb 27 '12 at 13:15

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.