Formula Help on Interpolation

[YuriTulchinbork]

So, I've searched and searched through what seems to be thousands o
threads on here and the net, with little understanding of what it i
that I have to enter or do to Interpolate... So I'm a very, and I mea
very basic user of excel, so breaking a response down would be great!
Heck pictures would be the absolute best lol, but not asking fo
that.... just a little guidance. So,

What I'm trying to do is select values within my cells that I hav
pasted into excel from information collected from a vehicle to calibrat
the Mass Airflow Sensor *MAF*. This information has to be not onl
smooth, but interpolation would create a great linear graph rather tha
me using the calculator on my phone anymore to figure it out... which i
fine if my goal is out of this world.

So an example would be if i want to interpolate the numbers from H2 t
M2 what would I use as a formula to do so, and put those values into th
cells H2 through M2, maybe have the numbers change color so i notice
change as well? I apologize if this seems like a really hard request, i
does to me lol.

Instructions/Examples/Modification to my document would be highl
appreciated.... I figure if i can do it on paper with my cell, and exce
document should be able to one up me. Oh I'm using Windows 7 Premium
Microsoft Excel 2007, and a HP G72 laptop if that makes any difference

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Living the Dream]

Hi

I found this:

=LINEST(known_y's,known_x's,const,stats)

This is a video link to give you a visual guide on how to do it.

There is also 2 other example of X Y calculations.

=Trend(Known_y's, Known_x's)
&
=Intercept(Known_y's, Known_x's)

Sorry, beyond this, I have no knowledge.

HTH
Mick.

 

[YuriTulchinbork]

Hi

I found this:

=LINEST(known_y's,known_x's,const,stats)

This is a video link to give you a visual guide on how to do it.

There is also 2 other example of X Y calculations.

=Trend(Known_y's, Known_x's)
&
=Intercept(Known_y's, Known_x's)

Sorry, beyond this, I have no knowledge.

HTH
Mick.

Thank you very much Sir, using the trend has helped out but it stil
isn't coming out with a smooth transition between two points. I need t
be able to grab the cells and know what they would look like togethe
smoothed out I guess between the points I select. I don't know if I'
asking the question right.

If I grab 3 cells, and dont care what the cell #2 says, rather I wan
the three points to average out to something smooth, for example If cel
one has a value of 0.00, two has a value of 0.56, and cell three has
value of 1.00, I know that cell two should be .50, not .56 which woul
create a much smoother graph between the two points that have bee
selected... I guess once I figure that part out it should be easy to d
up to 10 cells at once.

Can someone help me out here? Or just tell me im in over my head lol

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Living the Dream]

Look at the Round() Function.

You can round up or down to whatever your desired output you want.

Cheers
Mick.

 

[Ron Rosenfeld]

Thank you very much Sir, using the trend has helped out but it still
isn't coming out with a smooth transition between two points. I need to
be able to grab the cells and know what they would look like together
smoothed out I guess between the points I select. I don't know if I'm
asking the question right.

If I grab 3 cells, and dont care what the cell #2 says, rather I want
the three points to average out to something smooth, for example If cell
one has a value of 0.00, two has a value of 0.56, and cell three has a
value of 1.00, I know that cell two should be .50, not .56 which would
create a much smoother graph between the two points that have been
selected... I guess once I figure that part out it should be easy to do
up to 10 cells at once.

Can someone help me out here? Or just tell me im in over my head lol.

You are not describing clearly enough what you want. I don't know how you "know" that cell two should be 0.50 and not some other value.

In particular, if you have a group of points and want to draw a straight line through those points, TREND will do that using the least squares method. If your data is better fitted by an exponential or polynomial type of curve fitting, there are ways of doing that also.

But in your above description, you seem to be wanting to completely exclude the value of 0.56, but include the values 0.00 and 1.00. Without knowing the criteria that leads you to include the latter, and exclude the former, it is difficult to supply you with an answer.

The TREND function can account for either forcing or not forcing the intercept to be zero.

As an example, if your known Y's are 0.00, 0.56 and 1.00; and your known X's are 0, 1, and 2, a straight line, computed using the least squares method, through that data returns: 0.02, 0.52 and 1.02. If you force the intercept to be zero, then the straight line for those points would pass through 0.00, 0.512 and 1.024.

So, if curve-fitting using the least squares method is not what you want, I think you need to think more about exactly what you want, and describe it more accurately here.

 

[joeu2004]

If I grab 3 cells, and dont care what the cell #2 says,
rather I want the three points to average out to something
smooth, for example If cell one has a value of 0.00, two
has a value of 0.56, and cell three has a value of 1.00,
I know that cell two should be .50, not .56 which would
create a much smoother graph between the two points that
have been selected... I guess once I figure that part out
it should be easy to do up to 10 cells at once.
Can someone help me out here?

It might help if you uploaded an Excel file with example data, and maybe
even with a column that shows the results that you expect. Of course, you
would need to do the latter manually. See instructions below.

Perhaps you have heard the rule "two points define a straight line". That
sounds like what you are trying to do there. Certainly, if we define a line
with (1,0) at one end and (3,1) at the end other, (2,0.5) is the correct
interpolated midpoint.

The notation (x,y) is the x-axis value and the dependent y-axis value
derived from it. The x-axis "value" might simple be the ordinal position
(1, 2, 3,...) of the y-axis data.

We could use the TREND formula to derive that point. But FORECAST is the
more straight-forward function for this purpose. So if X1:X3 contains the
values 1, 2 and 3, Z1 might contain either of the following formulas:

=TREND({0,1},{1,3},X1)
=FORECAST(X1,{0,1},{1,3})

Copy that formula into Z2:Z3. You will notice that Z2 is indeed 0.5.

Similarly, if Y1 is 0 and Y10 is 4, we might put the values 1 through 10
into X1:X10 and the following formula into Z1:Z10 (Z1 shown):

=FORECAST(X1,{0,4},{1,10})

However, that presumes that a straight-line through the endpoints is a good
estimation of the data. It might be. It might not be.

It would be best to start by graphing your data. Experiment with the Chart
trendline options to see which gives you the best fit. (But generally,
avoid the temptation to use high-degree polynomial trendlines.)

What I'm trying to do is select values within my cells that I have
pasted into excel from information collected from a vehicle to calibrate
the Mass Airflow Sensor *MAF*. This information has to be not only
smooth, but interpolation would create a great linear graph [....]
So an example would be if i want to interpolate the numbers from H2 to
M2 what would I use as a formula to do so, and put those values into the
cells H2 through M2, maybe have the numbers change color so i notice a
change as well?

Changing the color is a completely separate issue. And it is not even clear
how you want to assign colors. In any case, that involves Conditional
Formatting. Let's put that part problem aside.

As for a straight-line approximation of the MAF data, you might put the
numbers 1 through 5 into H3:M3. Then put the following formula into H4:M4
(H4 shown):

=TREND($H$2:$M$2,,H3)

But you will probably notice that the values in H4 and M4 are not the same
as H2 and M2, for example.

This is because all of these functions -- TREND, FORECAST, LINEST, etc --
return points along a "best fit" line that is determined statistically. See
the Remark section in the FORECAST help page for details.

The intent is to find the straight line that, on average, is the least
distant from the corresponding data.

Again, it is much easier and it would be much more instructive for you if
you uploaded an example Excel file (devoid of any private data) that
demonstrates the problem to a file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here. The following is a list of some free file-sharing
websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

 

[joeu2004]

Similarly, if Y1 is 0 and Y10 is 4, we might put
the values 1 through 10 into X1:X10 and the
following formula into Z1:Z10 (Z1 shown):
=FORECAST(X1,{0,4},{1,10})

That should be 4.5, not 4.