# Estimate Linear Formula from Multiple Coordinate Pairs

#### Joe4

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.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.

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).

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.

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

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.

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

I missed that. Works now. Thanks.

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

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.

Replies
4
Views
555
Replies
3
Views
505
Replies
4
Views
464
Replies
0
Views
647
Replies
1
Views
375

1,207,199
Messages
6,077,021
Members
446,251
Latest member
dpf220

### 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.

### Which adblocker are you using?

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

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