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?action=getinfo&userid=23820
View this thread: http://www.excelforum.com/showthread.php?threadid=374752

0
5/28/2005 2:28:55 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
389 Views

Similar Articles

[PageSpeed] 34

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?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=374752

0
5/28/2005 3:16:51 AM
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?action=getinfo&userid=23820
View this thread: http://www.excelforum.com/showthread.php?threadid=374752

0
5/28/2005 3:59:03 AM
LINEST function will probably help you do this. Excel's HELP has a nic
explanation on how to use this function.

Regards

--
BenjieLo

-----------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1101
View this thread: http://www.excelforum.com/showthread.php?threadid=37475

0
5/28/2005 4:21:37 AM
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" <Tatsukun.1pq4ef_1117253108.7673@excelforum-nospam.com> wrote in 
message news:Tatsukun.1pq4ef_1117253108.7673@excelforum-nospam.com...
>
> 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?action=getinfo&userid=23820
> View this thread: http://www.excelforum.com/showthread.php?threadid=374752
> 


0
bliengme5824 (3040)
5/28/2005 1:03:04 PM
Reply:

Similar Artilces:

Bolding/etc based on formula results...
Hi folks, I want to insert a formula into a cell, whereby if the results were outside a certain range, the results would be bolded, and the cell possibly infilled with a different background colour. If the results of the formula fell within the certain range, then the results would appear as normal type, and the background colour would be the same as the rest of the spreadsheet. Any pointers on how I can do this? Cheers, Dave A formula cannot change a cell's format But Format | Conditional Formatting can do just what you want. Have a look at it, experiment, then return with questions...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

I need to build a computer, and list the parts and prices.
I need to build an expensive computer, and a reasonable computer. How can I list the parts and prices in the excel program? Create a list with part names in one column, and prices in the next column, e.g.: Part Price 4X DVD+/-RW Drive $250 Then, on another sheet, you can create a dropdown list of parts by using data validation. There are instructions here: http://www.contextures.com/xlDataVal01.html To return the price for the selected part, you can use VLookup formulas. There are instructions here: http://www.contextures.com/xlFunctions02.html xoner8ed wrot...

Office Professional 2007 trial..need help
I purchased a 180 day trial version with disc in Jan 2010, installed into computer and the motherboard died. I purchased new computer with Windows 7 (previous computer was Vista) and tried installing and it won't work. I uninstalled everything Office and tried downloading the 60 day trial, still won't work as I get the activation expiration date of Dec. 31, 2002. Any ideas as now when I try to download from the website, it tells me I have downloaded my alotted number of times - still have no working programs and need them for a class - must be microsoft 2007 pro. Any help...

How do I create a poster consisting of 4 x A4 pages which get glue
I know this can be done as I have done it some yaers before. It may have been in Power Point however. As I remember I created the poster content on the word document page and then using some option/s one is able to segment the page into 4 quarters, print these and then glue them together. I am using Microsoft Office Student and Teacher 2003 Word cannot create posters - try Publisher. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP...

How do I make the x axis data the y axis data? #2
My graph automatically makes certain data the y axis and other data the x axis. I need to just reverse it for the graph I am looking for. Any suggestions? Hi Sarah, you can change the Source data of the chart by right- clicking it. There you can define the columns / rows for the x- and the y-axis. Best Markus >-----Original Message----- >My graph automatically makes certain data the y axis and other data the x >axis. I need to just reverse it for the graph I am looking for. Any >suggestions? >. > ...

Formula for adding up columns, i.e., B1:B10 + D1:D10 + F1:F10 + H1:H10 + J1:J10
I've never figured this one out. Went and googled yet again but I'm obviously not googling for the right thing. Same as with the help file. What is the type of formula we use for adding up columns or rows, esp. if they're broken up by rows/columns in between? Thanks! =SUM(B1:B10,D1:D10,F1:F10,H1:H10) -- Regards, Peo Sjoblom "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message news:ORPkLEa1FHA.2792@tk2msftngp13.phx.gbl... > I've never figured this one out. Went and googled yet again but I'm > obviously not googling for the right t...

How to get rid of "A formula in this worksheet contains one or more invalid references"
Frequently I'll delete some cells in an Excel 2007 worksheet and from then on get the message "A formula in this worksheet contains one or more invalid references." whenever I try to do anything in the worksheet. There are no invalid references, as I have verified. Possibly there were some temporarily while Excel was in the process of deleting the cells. If I save the file, exit Excel, then reopen the workbook, Excel changes its mind and decides there aren't any invalid references after all (at least the message goes away). Is there any way to get Excel to figure t...

how do you delete cells when they are connected with a formula
All I know how to do is enter info into a cell. This is a list of numbered employees. Usually, I would just seleted the cell and delete, but I get ###error. The formula is =+sum (A73+1) Please help!!! I believe you are seeing ### because the cell is not large enough to display the #REF! error. When you delete the cell (A73), either by right clicking and seleting delete or by using the Edit function, the formula no longer knows what cell to reference, and all the other cells below the one with the error have all adjusted accordingly (in this case, up one). If you look below the cell w...

Can i use conditional formating on a cell when it contains a formula? #2
=mid(text(a1;"0000000000.00");1;1 -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi the reason for this is that thie formula returns a string and not a numeric value. Change the formula to: =--mid(text(a1;"0000000000.00");1;1) -- Regards Frank Kabel Frankfurt, Germany sit wrote: > =mid(text(a1;"0000000000.00");1;1) OR ... Change your Conditional formula to: =&quo...

help with distribution formula!
i need to make a distribution table. i know that i use the -normdist- function, i just can't figure out what to put in each argument. my data is 10 questions, and each question has 5 answers to choose from (multiple choice). Assuming that im making complete random guesses, i need to find the probability of getting 1 question correct, 2 questions, 3, 4, and so on until i get the prob. of getting all 10 correct. what would i plug into each argument? x - mean - standard Dev - cumulative - i'm not specifically asking for the answers, but more for help on figuring out which goes into...

Vlookup Formula #3
Hi, column J has dates (days of each month). Column K has numbers. the whole rang is J7:K38. E12 has a date and F12 has a number. In cell G12, I need to: =F12*K? (that has the corresponding date as in cell E12) ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 and take the corresponding number in range K7:K39 and multiply it with the value in f12 and put the answer in cell G12. All in the same s Khalil] Hope to have an answer!! =VLOOKUP(E12,J7:K38,2,FALSE)*F12 if you indent to copy the formula down the column =VLOOKUP(E12,$E$7:$K$38,2,FALSE)*F12 best wishes -- Be...

Need assistance with Detailsview and passing values to a textbox
Hi I have a Multiview with two views. Both views each have a detailsview control with several textbox's and AutoGenerateEditButton="True". During Update I am trying to insert a value to the product textbox based on the option chosen from a dropdown but am having problems. Could someone please help. The error is for Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: Line 545: DirectCast(DTLTrainUpdateVw.FindControl("txtTrainProduct"), TextBox).Text = product Line 546: I get t...

Formulas are inactive
I have completed a five sheet program for monitoring Mutual Fund Data. All of the formulas acted normally until I completed a sixth sheet to summerize the data. The formulas on the sixth sheet link back to the other five sheets. Upon completetion of the sixth sheet none of the formulas in the preceding five sheets will work. Help Pete Check out Tools>Options>Calculation. Is is set to "manual" or "Automatic"? Gord Dibben Excel MVP On Sun, 14 Dec 2003 10:51:10 -0800, Pete S <psimler@twmi.rr.com> wrote: >I have completed a five sheet program for monitorin...

Need help with OUTPUT parameters
Hi, I am trying to execute two child stored procedures from a parent one. The first stored procedure inserts a new company into Companies table and returns its CompanyID, the second stored procedure inserts an address into Addresses table and returns its AddressID and the third one assigns the address to the company. Everything works fine but when I run the parent stored procedure, I only want to return the CompanyID back to my application. However, the code below returns both CompanyID and AddressID. I'm using OUTPUT clause so that I can grab CompanyID and AddressID r...

Groupwise 5.x migration white paper???
Is there a white paper around that will assist in the migration from Groupwise 5.x to Exchange 2003? I can start the migration tool that comes with Exchange, but when it gets to the point where I need to select the Groupwise Domain, I can browse the entire NDS tree but it doesn't allow me to select anything (OK is always greyed out). Any help with this issue is greatly appreciated! A couple of good papers are on http://www.microsoft.com/technet/prodtechnol/exchange/2000/library/e2kngwcm.mspx http://www.microsoft.com/exchange/techinfo/interop/GroupWise.asp http://www.eu.microsoft....

SMTPreg.vbs eventsink blokking vital system mail replication
Hi I have a problem with an eventsink, I have three exchange 2003 servers, each sitting in its own routing group called inner, outer and middel RG, mail sendt from the inner server must be logged and blocked if not sendt from a valid email klient, so what is done is that all mail sendt from the inner server must travel through the middle server and the eventsink is instaleld on the middle server. this works just fine, mail gets blocked. But since what triggers the eventsink is absence of an outlook addon, all system mail sendt between the three servers gets blocked. I need to let system mail b...

Excel Formula #12
I am trying to have text and a formula reside in teh same box with the date in the format shown (23-May-04). Here is as far as I get... ="Todays date is "&TODAY() and the results I get is Todays date is 38131 Rick Rick, here you go: ="Today's date is "& TEXT(TODAY(),"dd-mmm-yy") -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "Rick" <ricky.sather@si-intl.com> wrote in message news:7c55e5aa.0405241127.3cebb0ab@posting.google.com... > I am trying to have text and a formul...

Need Customization Guide
Can someone please send me a customization guide for RMS 2.0. I need to build some Add-ins and custom reports... thanks, Keith If you are a MS Partner certified in RMS you can request it from Microsoft. It is not distribution. Marc "Keith" wrote: > Can someone please send me a customization guide for RMS 2.0. I need to > build some Add-ins and custom reports... > > thanks, > Keith > > Can someone please send me a customization guide for RMS 2.0. MY EMAIL : AKRAMORACLE@HOTMAIL.COM "Keith" wrote: > Can someone please send me a cust...

Need a solution
I'm trying to find a solution - Can publisher create a piece of artwork that would look like a box (we are spending a fortune designing boxes and we re-route artwork a billion times before it's done and I want to cut the expense side down) I think that I could have a marketing coordinator to build something to show a English side and a performance side - I might be able to cut down costs and aggravation. Any thoughts Publisher has an autoshape that is a 3D box. Not sure I know what you mean. Publisher is not a designer's dream tool, but it can be used for designing. A po...

Urgent Help needed :Transferring MA questionnaire
This has been compiled on an Access 2000 home pc and we wnat to transfer it to my laptop with XP Access 2003. Unfortunately it ended up with an 'mde' tag in the process. It is only the database its self and contains no queries or reports. A further problem is that in trying to build the database again on my laptop, in Access 2003, it will not allow it to be saved in 'save as' or in 'save' : it keeps coming up with "indexes must have names". What does this mean? Please HELP !! I must be able to write up my answers during the next week! Hi Jan, N...

Urgent Help Needed!!
I'm making a leagye table and I need to link a line graph up to the results of a team chosen by a combo box, and then read the positions that that team has been in during the season so that it shows the increse/decrease in the teams performace. Attachment filename: image2.jpg Download attachment: http://www.excelforum.com/attachment.php?postid=404739 --- Message posted from http://www.ExcelForum.com/ any ideas are welcome, i don't mind if i have 2 change anything to get it to work, i just need to do this a quite urgently? If needed to i will do it a...

Need formula to calculate time difference
I am certain there is a really simple way of doing this and I am just too stupid to figure it out... I just want to have Row 1 Column B show a start time I manually enter (say 9:15PM), then be able to enter 10 (ten minutes) in row 2 column A to show a value of 9:25PM in row 2 column B. Row 2 Column B has time value (9:20PM) Row 2 Column A has minutes allowed (20) In other words, add B2 and A3 and show them as a new time in B3. Any takers??? Hi, if you type just 10 on a2 the formula should be =b1+time(0;a2;0) if you type 00:00 is just b1+a2 hth regards from Brazil Marcelo "mar...

Adding TEXT in front of a DATE formula
Hello everybody, Is it possible to add text in front? I am not getting there with this setup. Can someone help? =LEFT&"Date Printed:"&TODAY() Thanks Ray, ="Date Printed: " & TODAY() Note this will show up as something like: Date Printed: 37903 Probably not what you wanted. But, you can use the TEXT function to format the date... ="Date Printed: " & TEXT(TODAY(), "MMMM DD, YYYY") Date Printed: October 9, 2003 ="Date Printed: " & TEXT(TODAY(), "MM/DD/YYYY") Date Printed: 10/09/2003 etc... Dan E "Ray...

Need help creating a counter
I am trying to complete the following in a query. It has two different calculations. Counter SKU Record_Counter 1 0783D 1 1 0783D 2 2 100 1 2 100 2 2 100 3 3 10110045 1 3 10110045 2 3 10110045 3 3 10110045 4 3 10110045 5 3 10110045 6 3 10110045 7 3 10110045 8 3 10110045 9 4 10110090 1 4 10110090 2 5 10110162 1 5 10110162 2 6 10110575 1 6 10110575 2 I assume you start with the second field and want to get the first and third one, right? I did tested the following (air code). May miss parenthesis or something like that. To get the third column, you can start with a driver table, a table,...