Reading through your comments and revisions to the question, there are a couple of things you want to do that aren't really covered in my previous answer. This answer will deal with those items, and I've included a step-by-step walk-through of how you would accomplish the whole interpolation process.
Inaccurate Data
You describe the process that generated the data as taking readings at a time interval, and the numbers are rounded times. The equation is only as good as the data. In your actual analysis, you should use the most precise numbers available (perhaps you were just keeping your example simple by showing rounded times).
However, the data you show don't precisely fit the kind of curve you typically see for a physical process. Theoretical curves are generally smooth when there is just one driving variable and no noise. If you are using very precise equipment both to trigger a reading at a preset interval and to provide an accurate measurement, you can accept the results as precise. However, if you are manually timing the reading and manually taking the reading, the X
values can be at imprecise times even if the readings, themselves, are accurate. Shifting individual X
values a little one way or the other will introduce the kinds of small irregularities you see in the curve of your data (unless the example is just numbers you made up for the purposes of an example).
If this is the case, you might benefit from using regression to estimate best fit.
Using Y as X
In your problem, you want to define values for Y
(integer values from 1 to 37 in this example), and find the associated X values. That was easy enough to do in your Y=2^X
problem because that simple equation can easy be reversed to X=log(Y)/log(2)
, and you can directly calculate any value you want. If the equation isn't something simple, there often isn't a practical way to invert it. The "abused" regression approach in my previous answer gives you a high order equation, but it's "one-direction", often not practical to solve for the reverse equation.
The simplest approach is just to reverse X
and Y
from the start. This gives you an equation you can use with the integer values you introduce (the analysis gives you the coeficients of the equation as described in the previous answer).
It never hurts to see if a simple curve will work. Here's the reversed data, and you can see that there's not a useful fit:
So, try a polynomial fit. However, this is a case like I described in the previous answer. The values from 1 through 8 fit well, but 9 gives it indigestion. A 3rd order polynomial gives you a bump:
It gets progressively more "interesting" as the order of the equation increases. By the 7th order, you get this:
It goes almost exactly through every point, but the curve between 8 and 9 isn't useful. One solution would be to make do with linear interpolation between 8 and 9. In this case, though, you could get better values by incorporating splines for the upper end. The splines option provides a good looking fit, and a curve that makes more sense between 8 and 9:
Unfortunately, the spline equations are a bit convoluted and the equations are not provided. However, you could do the linear interpolation on the intermediate values provided by the analysis, which should get you very close to numbers that fit a reasonable curve.
Extrapolation vs. Interpolation
In this example, your first Y
value is 2.9. You want to produce values for 1
and 2
, which are outside the range of the data. That requires extrapolation rather than interpolation, which is a very different requirement.
If the equation is known, like your Y=2^X
example, you can calculate any value you want.
If the process generating the data is known to follow a simple curve, and you are confident of the fit, you can project values outside the data range, and even get a meaningful confidence interval for the range that the values could actually be (based on how much variation there is between the data and the curve inside the data's range).
If you're force-fitting a high-order equation to the data, projections outside the data's range are usually meaningless.
If you are using splines, there is no basis for projecting outside the data range.
Whatever projections you make outside the range of your data are only as good as the equation you use, and if you're not using an exact equation, the farther you get from your data, the more inaccurate it will be.
Looking at the log curve in the first graph, you can see that it would project a very different value than what you would expect.
For the polynomial equations, the zero-power coeficient is a constant, and that's the value that would be produced for an X
value of 0
. So that's a simple way to look at where the curve would go in that direction.
Note that by the 4th or 5th order, the points 1 through 8 are pretty accurate. But once you go outside the range, the equations can behave very differently.
Extrapolation using limited data
One way to improve things is to fit only the points at that end, and include as many successive points as follow the shape of the curve at that end. Point 9 is obviously out. There are several inflections in the curve before that, one being around point 5 or 6, so points higher than that follow a different curve. Using just the points 1 through 5, you get close to a perfect fit with a 3rd order polynomial. That equation would project a zero point of 0.12095 (compare to the table above), and for an X
value of 1
, 0.3493
.
What happens if you just fit a straight line to the first five points:
That projects a zero point of -0.5138 and for an X
of 1
, -0.0071
.
That range of possible outcomes indicates the level of uncertainty outside the range of your data. There is no right answer. And this was at the "well-behaved" end of your curve. The Y
value for an X
of 9
is 36.7
. You want to go to 37. The splines suggest that the curve is asymptotic at 9
. Projecting a straight line in the raw data would produce a value a little more than 9
(same with a 4th order polynomial). A 3rd order polynomial suggests a value less than 9
(as do 5th and 6th orders). A 7th order polynomial suggests a value substantially above 9
. So anything outside of the data range is a guess, or anything you want it to be.
Putting it all together
So let's step through what the actual solution would look like. We'll assume you've already tried to find an exact equation and tested common curves using a trend line. The next step would be to try regression because that gives you the formula for the curve and you can plug in your integer values.
I don't have ready access to Excel 2013 or the Analysis Toolkit. I'll use LibreOffice Calc to illustrate this. It's not identical, but it's close enough that you should be able to follow it in Excel. In LO Calc, this is actually a free extension that needs to be loaded. I'm using CorelPolyGUI, which can be downloaded here. My recollection of the Analysis Toolkit is that it didn't include splines. If that's still the case and you want to do this in Excel, I came across this free add-in (which I have not tested). An alternative would be to use LO Calc, which will run in Windows and is free.
Here, I've entered the X and Y values (reversed) in columns A and B, and opened the analysis dialog. Highlighting the X values and clicking the X button loads the data ranges, and I've selected polynomial.
On the next tab, I specify that I want to use 0
to 7
degrees (a 7th order polynomial with all of the orders).
To specify the output, I select C1 and click Columns, and it registers the columns needed for the output. I select that I want it to output the original data, the calculated results, and I've selected to have it add three intermediate points between each original data point. And I tell it I want a graph of the results on a new chart. Then go to the calculate menu and click calculate.
And there it is. If you look at the calculated values, you may notice a problem. It will become apparent in the next step.
Here, I've added the 1
through 37
values. At this point, we only want to deal with interpolation, so I've added a formula to calculate only the values 3
through 36
. The formula just expands the coeficients listed in the results (the a(n) values). The formula in I2 is:
=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7
This is just each coeficient multiplied by the associated power of the X value. Drag this down and you have your results. Well not quite; you have to look at it to see if it passes the sanity test. We knew there was a problem between 8
and 9
, but that turns out to be half of the values you want. We could use the values from 3
through 20
, but there's no sense in combining that many values from another method. So let's just use splines for the whole thing.
Open the analysis dialog again, and change the method to "splines" on the input tab (not shown here). Give it a new output range and tell it to calculate. That's all it takes.
We have new results to work with. Dividing the data range into this many segments keeps each segment short, so linear interpolation should be pretty good (way better than using it on the original data).
The process of curve fitting or interpolation involves creating data points; using your own judgement about what the curve "should" (or should not), look like (regression assumes that even the original data are imprecise).
Giving this data a sanity check shows that even splines produce a connecting curve with a bulge; one value goes slightly over 9
, which is likely an artifact rather than a reflection of the process you were measuring. In this case, a curve asymptotic at 9
is more likely, so I arbitrarily assigned the high point a value that's a hair less than 9
by eyeballing it. The assumption isn't that my value is precise, only that it's an improvement. For this illustration, I created a new column with the values that will be used.
I added a column with your numbers 1
through 37
. From the previous discussion, we don't have a reliable basis for projecting values for 1
and 2
, so I left them blank. For 37
, I went with the asymptotic assumption and made it 9
. The values for 3
through 36
are found by linear interpolation (and it's a formula you could adapt to other data). The formula in Q3 is:
=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)
The TREND function just interpolates when the range is two points. The syntax is:
TREND(Y_range, X_range, X_value)
The OFFSET function is used for each range. In each case, it uses the MATCH function to find the first row of the range containing the target value. The -1
values are because these are offsets rather than locations; a match in the first row is an offset of 0
from the reference row. And note that the Y
column is offset by 2
, in this case, because I added an extra column to manually adjust a value. The OFFSET parameters pick the column containing the Y or X values, and select a range height of 2, which gives you the values below and above the target.
The result:
The analysis wizard does the heavy lifting, and whether you're using polynomial regression or splines, it required just one formula to generate the result.
TREND
function to calculate the Y values