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 nice 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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tatsukun" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Tatsukun
> ------------------------------------------------------------------------
> Tatsukun's Profile:
> http://www.excelforum.com/member.php...o&userid=23820
> View this thread: http://www.excelforum.com/showthread...hreadid=374752
>