To understand how Excel handles regression information lets follow this example of perfect correlation:
Assume you want to buy baseballs that cost $5 each. You can buy any number of balls.
Example 1: Below is a list of the total cost for the quantity purchased.
In Perfect correlation. You can almost "see" the relationship!
Baseball cost $ 5.00 STEPS:
Dependent -X Independent - Y 1 Enter your data, as in A7 to B12 or A25 to B
5 $ 25.00 2 1st column are X values 2nd column are Y
6 $ 30.00 3 Higlight the two data columes and insert th
7 $ 35.00 The data is squenced and graphed.
8 $ 40.00 4 right click on any data point
9 $ 45.00 5 Select Add Trendline
10 $ 50.00 Check the option to show the equation.
11 $ 55.00 Check the option to show the R-Square
12 $ 60.00
13 $ 65.00 y=mx+b straight line formula
14 $ 70.00
15 $ 75.00
16 $ 80.00
17 $ 85.00 5 slope
Example 2:
In this set the Perfect correlation is not as easy to see.
Same Data but not in order:
Dependent -X Independent - Y
$90.00
14 $ 70.00
6 $ 30.00 $80.00
15 $ 75.00 $70.00
17 $ 85.00 $60.00
7 $ 35.00 $50.00
10 $ 50.00 $40.00
8 $ 40.00 $30.00
9 $ 45.00 $20.00
11 $ 55.00
$10.00
13 $ 65.00
$-
16 $ 80.00
12 $ 60.00 0 5
5 $ 25.00 5
Example 3:
Still using perfect correlation let's add Shipping costs to each order of 7.98 STEPS:
1 Enter your data, as in A44 to B5
Dependent -X Independent - Y Independent - Y 2 1st column are X values 4th col
14 $ 70.00 7.98 $ 77.98 3 Higlight the two data columes a
6 $ 30.00 7.98 $ 37.98 The data is squenced and grap
15 $ 75.00 7.98 $ 82.98 4 right click on any data point
17 $ 85.00 7.98 $ 92.98 5 Select Add Trendline
7 $ 35.00 7.98 $ 42.98 Check the option to show the
10 $ 50.00 7.98 $ 57.98 Check the option to show the
8 $ 40.00 7.98 $ 47.98
9 $ 45.00 7.98 $ 52.98 $100.00
11 $ 55.00 7.98 $ 62.98
$80.00
$80.00
13 $ 65.00 7.98 $ 72.98
16 $ 80.00 7.98 $ 87.98 $60.00
12 $ 60.00 7.98 $ 67.98
$40.00
5 $ 25.00 7.98 $ 32.98
$20.00
$-
0 5
r data, as in A7 to B12 or A25 to B37.
n are X values 2nd column are Y values
e two data columes and insert the Scatter graph type
s squenced and graphed.
on any data point
the option to show the equation.
the option to show the R-Square value.
straight line formula
y = 5x
R² = 1
Series1
Linear (Series1)
10 15 20
Enter your data, as in A44 to B56
1st column are X values 4th column are Y values
Higlight the two data columes and insert the Scatter graph type: Hint: use the control key.
The data is squenced and graphed.
right click on any data point
Select Add Trendline
Check the option to show the equation.
Check the option to show the R-Square value.
y = 5x + 7.98
R² = 1
R² = 1
Series1
Linear (Series1)
10 15 20
Use the following data sets to practice:
Data set #1 Place graphs in this area.
Year Rainfall Bushels of Wheat
Inches In millions
1993 34 123
1994 37 143
1995 42 152
1996 32 120
1997 33 125
1998 34 126
1999 35 145
2000 36 153
2001 31 127
2002 35 141
2003 33 128
2004 32.5 122
2005 32 116
2006 31.5 110
2007 31 104
2008 36 ?????
What is the expected harvest for 2008?
What is you R-Square?
Data set #2
Year Advertising Sales
by Qtr. Budget
1997 -1 $ 27,635 $ 18,790,503
1997 -2 $ 304,770 $ 136,752,630
1997 -3 $ 739,771 $ 291,127,079
1997 -4 $ 775,680 $ 376,775,506
1998 -1 $ 917,244 $ 390,933,778
1998 -2 $ 1,061,315 $ 431,861,154
1998 -3 $ 1,307,199 $ 493,291,421
1998 -4 $ 1,383,887 $ 549,782,030
1999 -1 $ 1,397,614 $ 569,211,613
1999 -2 $ 1,536,446 $ 657,289,999
1999 -3 $ 1,678,550 $ 791,639,866
1999 -4 $ 1,753,809 $ 838,869,108
2000 -1 $ 1,867,586 $ 854,742,040
2000 -2 $ 1,995,092 $ 866,016,903
2000 -3 $ 2,169,740 $ 925,665,948
2000 -4 $ 2,299,281 $ 1,037,199,081
2001 -1 $ 2,400,170 $ 1,060,764,254
2001 -2 $ 2,449,997 $ 1,066,632,978
2001 -3 $ 2,496,938 $ 1,356,245,663
2001 -4 $ 2,639,828 $ 1,391,940,620
2002 -1 $ 2,695,411 $ 1,647,111,995
2002 -2 $ 3,151,992 $ 2,139,953,147
2002 -3 $ 3,158,647 $ 2,153,605,037
2002 -4 $ 3,196,900 $ 2,225,557,127
2003 -1 $ 3,211,009 $ 2,351,345,987
2003 -2 $ 3,480,293 $ 2,796,090,947
2003 -3 $ 3,488,841 $ 2,953,313,715
What would you expect sale to be if your add budget this year is $275,000
What is you R-Square?
Data set #3
Cost to dig a ditch:
Length in feet Cost
320 $ 42,400
2230 $ 281,150
47 $ 8,275
100 $ 14,900
432 $ 56,400
1345 $ 170,525
5432 $ 681,400
2378 $ 299,650
12 $ 3,900
1358 $ 172,150
4567 $ 573,275
2381 $ 300,025
4298 $ 539,650
2314 $ 291,650
3476 $ 436,900
765 $ 98,025
489 $ 63,525
8000 $ 1,002,400
231 $ 31,275
2347 $ 295,775
What would you bid for a 100 foot long ditch? How much do you think your "set-up" costs are?
What is you R-Square?
Place graphs in this area.