Have X's and Y'x, need formula

[Tatsukun]

Hi all, I hope you can help an old dog learn a new trick here. I have a
list of 20 values for X, and thier matching Y values. Now, what I need
to do is find a way to get a formula so I can put in a different X and
get a Y.

I hope that's specific enough, I'm not sure how open I can be with this
data.

I am using Excel 2000.

Thank you all in advance!

-Tatsu

 

[BenjieLop]

Hi all, I hope you can help an old dog learn a new trick here. I have a
list of 20 values for X, and thier matching Y values. Now, what I need
to do is find a way to get a formula so I can put in a different X and
get a Y.

I hope that's specific enough, I'm not sure how open I can be with this
data.

I am using Excel 2000.

Thank you all in advance!

-Tatsu

ASSUME that:

Cells A1:A20 contain your X values and
Cells B1:B20 contain the corresponding Y values
Cell C1 is where you will enter the X value

Given the above assumptions, your formula is (enter, say, in Cell D1):

=VLOOKUP(C1,A1:B20,2,0)

Hope this is the formula that you are looking for.

Regards.

 

[Tatsukun]

Thanks, that seems to work for some of the cells, but I get a lot of Y
values of "N/A".

To clarify, I want to enter X values that are not already entered, and
have Excel calculate a Y (or a reasonable guess thereof).

So for example, if my X values are 2,4,6,8, and 10; and my
correcponding Y's are 10,20,30,40, and 50 respectivly, I want to be
able to input something like "X=3" and get out "Y=15".

I really wish I knew enough about this stuff to make sence trying to
explain my problem.

I asked my son, he got me as far as a scatter graph, and a Trendline.
So I got this really hard looking formula...

y = 2E-16x4 + 2E-11x3 + 8E-06x2 + 0.6827x + 731.29

Can I just make Excel work that out somehow?

Thanks!

-Tatsu

 

[BenjieLop]

LINEST function will probably help you do this. Excel's HELP has a nic
explanation on how to use this function.

Regards

 

[Bernard Liengme]

Your son has put you one the right track with the trendline. But look at the
first three terms in the equation y = 2E-16x4 + 2E-11x3 + 8E-06x2 + 0.6827x
+ 731.29
They are so small compared to the others that I think you would be better
of with just two terms y = 0.6827x + 731.29 UNLESS you have some very large
x values.

To get the slope value in a cell use =SLOPE(y-value-range, x-value-range).
Lets say this is in D10
To get intercept use =INTERCEPT(y-value-range, x-value-range). Lets say
this is in E10
To find y's value when x=3: put 3 in F10 and in G10 use =F10*D10+E10 (the
equation of a straight line is y=mx+b)

If you want more terms use LINEST. Visit
www.stfx.ca/people/bliengme/ExcelTips on how to do this
best wishes