Estimate Linear Formula from Multiple Coordinate Pairs

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Believe it or not, I have been using Excel for years, and have never had to do this.

I have 32 pairs of coordinates (X,Y). If I graph them using a scatter chart in Excel, I can see that it looks like they suggest a linear relationship between the points.

Other than printing the chart and trying to draw a "best fit" line manually, is there some sort of way I can get Excel to come up with the "best" linear formula (y=mx+b format)?

Thanks.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
You can always add a trendline.

Right click on a data point, add trendline, go to options, display equation on chart.

alternatively, you could use the following array formula enterd across two cells.

=LINEST(B1:B32,A1:A32)

to get the slope and y-intercept without having to graph.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply, but I think I need a few follow-up questions (you can probably tell that I never work with charts).

Option 1
I can add the trendline, but I do not see where/how I can display the equation on the chart.

Option 2
The LINEST function only seems to return one value. Is this the slope or the y-intercept (for some reason, the LINEST function doesn't appear in Excel help, maybe I'll do a web search for it).
 
L

Legacy 14611

Guest
To use LINEST go to Insert->function on the excel menu. LINEST is a statistical function.

There's a link to Help on this Function which you can check.

LINEST is an array function, which gives the coefficients of y=mx+c (although often in a somewhat bizarre order) along a row, with other statistical output conceringthe LINEST (test statistic etc) appearing below.

Select a range of (in your one RH variable case) of 2 columns and 5 rows, insert LINEST and press ctrl-shift-enter.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi Joe

Thru an amazing coincidence, I have had cause to review trend lines etc this weekend (I don't use charts much either).

When you have created your scatter graph, click on the plots and go Add Trendline. In the dialog that opens, choose the Options tab and here you can check to display the formula and the R squared value if required.

To extend the data table (ie to predicted values) you can use the Trend function:

Excel Workbook
AB
1RunValue
21400
32450
43499
54560
65590
76620
87710
98740
109800
1110870
1211903
1312953
14131004
15141055
Sheet2
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
rugila and Richard,

Thanks for the replies. I was able to get the formula with a little help from Richard's clarification of where the Options tab was (I was trying to do the Trend line and formula separately, not realizing the Options box was in that same dialog).

I see that if you use the function icon (fx), it will display the two variables (slope, y-intercept) in that dialog that box. I find it very odd, though that when it displays the results on the spreadsheet, it only shows the y-intercept!

In any event, I got what I need. Thanks to everyone for the help.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Joe

Did you follow rugila's advice about selecting a range of 2 columns' width before array entering your LINEST formula? You should get a result like:

Excel Workbook
ABCD
1RunValuem valueY intercept
2140050.69697345.06667
32450
43499
54560
65590
76620
87710
98740
109800
1110870
Sheet2
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Did you follow rugila's advice about selecting a range of 2 columns' width before array entering your LINEST formula?
I missed that. Works now. Thanks.

Boy, it would be nice if Excel had some help/examples on this function.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yes - it returns a horizontal array of values, the first of which is the 'm' and the second of which is the y-intercept (ie from the formula y = mx + C).

Crucially, you need to select the entire destination range prior to entering the formula, and array-enter it when you do enter it.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top