Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Have X's and Y'x, need formula
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 ------------------------------------------------------------------------ Tatsukun's Profile: http://www.excelforum.com/member.php...o&userid=23820 View this thread: http://www.excelforum.com/showthread...hreadid=374752 |
#2
|
|||
|
|||
Tatsukun Wrote: 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. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=374752 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
LINEST function will probably help you do this. Excel's HELP has a nice explanation on how to use this function. Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=374752 |
#5
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |