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.

 

[PA HS Teacher]

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]

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

 

[Legacy 14611]

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]

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
	A	B
1	Run	Value
2	1	400
3	2	450
4	3	499
5	4	560
6	5	590
7	6	620
8	7	710
9	8	740
10	9	800
11	10	870
12	11	903
13	12	953
14	13	1004
15	14	1055
Sheet2

 

[Joe4]

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]

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
	A	B	C	D
1	Run	Value	m value	Y intercept
2	1	400	50.69697	345.06667
3	2	450
4	3	499
5	4	560
6	5	590
7	6	620
8	7	710
9	8	740
10	9	800
11	10	870
Sheet2

 

[Joe4]

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]

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]

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.

Looked at the help file? It contains several good examples.

You may also want to look at
Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/publish_train/data_analysis/16.htm