Have X's and Y'x, need formula

  • Thread starter Thread starter Tatsukun
  • Start date Start date
T

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
 
Tatsukun said:
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
Click to expand...

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.
 
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
 
LINEST function will probably help you do this. Excel's HELP has a nic
explanation on how to use this function.

Regards
 
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
 
Back
Top