Hi to all,
I have data like this
A B
3.52 19.13
3.53 25.38
3.878696 ?
3.952392 ?
3.964257 ?
4.26 26.96
4.50798 ?
4.790395 ?
5.48 34.87
now i want to guess the values in ???? (question marks) places based on the values in col A? how can i do this excel? i heared some where like using linear regression we can do this in excel but not so clear about it. can any one help me in this regards? thanks lot and any help would be highly appreciated,
Regards,
Genetist
Split your data - put the 4 known pairs in A1:B4 (the yellow cells), and the unknown X values into column C starting in C1 (the orange cells). Then in D1, use this formula to give the corresponding Y value of interest (the green Cells):
=SLOPE($B$1:$B$4,$A$1:$A$4)*C1+INTERCEPT($B$1:$B$4,$A$1:$A$4)
Then copy down to match column C.
Note that you will not get a very good fit, since you have two close X values (3.52 and 2.53) with very different Y values (19.13 and 25.38). I repeated the X values at the bottom of column C in the attached picture, to show what you should end up with (the blue cells)..
Capture.JPG
Last edited by Bernie Deitrick; 08-18-2014 at 09:32 AM.
Bernie Deitrick
Excel MVP 2000-2010
Hi Deitrick,
Thank you very much for your help to solve my problem. Can you please explain me the formula, i understood slope and intercept and i did not get slope multiplied with C1 value. Why we should multiply slope with C1 value?.
Thanks lot for your help
Regards,
genetist
The equation of a straight line in standard form is
Y = m*X + b
Where m is the SLOPE and b is the INTERCEPT.
Since C1 has your known (X) value, that formula gives the unknown (Y) value.
Hi Deitrick ,
thank you very much for your explanation and your reply really working nice
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks