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

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 rather than me using the calculator on my phone anymore to figure it out... which is fine if my goal is out of this world.

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? I apologize if this seems like a really hard request, it does to me lol.

Instructions/Examples/Modification to my document would be highly appreciated.... I figure if i can do it on paper with my cell, and excel 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.

apparently I can't attach the file so hopefully this screen shot will help

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.

http://www.youtube.com/watch?v=26-q2BeyquQ


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 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.

Look at the Round() Function.

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

Cheers
Mick.

On Wed, 29 Aug 2012 03:13:00 +0000, YuriTulchinbork wrote:

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.

"YuriTulchinbork" wrote:
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.)


"YuriTulchinbork" wrote:
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
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

Errata (typo).... I wrote:
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.