6.
Mars and Neptune
This
section offers you further opportunity to use Excel’s Trendline
to find the curve of best fit.
|
Because
the gravity is different on different planets, your weight
will be different on different planets (your mass
will remain the same).
The Excel
worksheets below will convert a weight on Earth (x),
to the corresponding weight M(x)
on Mars and N(x)
on Neptune.
Your task
is to use Excel’s Trendline to find the formulae Excel
is using, and then use these formulae to predict M(78) and
N(78). Answers are also provided in Excel.
|
|
We can use technology
regression tools to find the formulae not only for approximate data,
but also to find exact formulae instead of using algebraic methods.
For exact
formulae the predicted value (y')
is equal to the observed value (y),
so all errors y
– y'
= 0 and R2 = 1.
The Excel workbook
below contains separate worksheets (click on the TABS at the bottom of
the window) for the following tables of data. In each case, find the formula
y
= f(x)
in two ways: using algebraic
methods (or you can just see it), and using Excel’s
Trendline.
Use the one method to check
the other.
Use the formulae to find the missing values – enter your values
in Excel and Excel will give you feedback.
| 1. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
1 |
4 |
9 |
16 |
25 |
36 |
49 |
|
|
2. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
1 |
3 |
7 |
9 |
11 |
13 |
15 |
|
|
| |
|
|
|
| 3. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
3 |
6 |
11 |
18 |
27 |
38 |
51 |
|
|
4. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
1 |
3 |
6 |
10 |
15 |
21 |
28 |
|
|
| |
|
|
|
| 5. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
2 |
10 |
30 |
68 |
127 |
218 |
350 |
|
|
6. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
27 |
24 |
21 |
18 |
15 |
12 |
9 |
|
|
| |
|
|
|
| 7. |
x |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
20 |
y |
6 |
12 |
24 |
48 |
96 |
192 |
394 |
|
|
8. |
x |
0 |
20 |
40 |
60 |
80 |
90 |
100 |
35 |
y |
32 |
68 |
104 |
140 |
176 |
194 |
212 |
|
|
Click here to open
the Excel Formulae tool: 
|
