[go: up one dir, main page]
Scribd
Upload
82 views9 pages

Non-Linear Regressions by Using The Solver: A "Just Add Data " Excelet

This document provides instructions and examples for using Excel's Solver tool to perform non-linear regressions on datasets. It contains tabs with examples of sine, logistic, and exponential decay regressions, showing the data, regression equations, parameters fitted by Solver, residuals plots and summaries. Notes provide guidance on using Solver, handling different numbers of data points, and interpreting the regression results and residual plots.

Uploaded by

Nathan Davidson-pilon
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd
82 views9 pages

Non-Linear Regressions by Using The Solver: A "Just Add Data " Excelet

This document provides instructions and examples for using Excel's Solver tool to perform non-linear regressions on datasets. It contains tabs with examples of sine, logistic, and exponential decay regressions, showing the data, regression equations, parameters fitted by Solver, residuals plots and summaries. Notes provide guidance on using Solver, handling different numbers of data points, and interpreting the regression results and residual plots.

Uploaded by

Nathan Davidson-pilon
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd
You are on page 1/ 9

Excelet

Non-linear Regressions by Using the Solver


A "Just Add Data " Excelet
Adjust the parameters in the yellow cells to get the curve close to the data
On each tab, go to Tools on the menu bar, select Solver (already set up).
When the Solver Parameters menu pops-up, click the Solve button.

Solvers needs to be loaded - see notes tab!


Click the tabs to navigate!!!

close to the data.

already set up).

Sine Regression
y = A sin (B (x - h)) + k
x
1
2
3
4
5
6
7
8
9
10
11
12

ymeasured

ypredicted

3.7
3.8
7.4
11.6
21.1
24.5
27.1
26.7
26.1
19.9
11.4
7.9

3.61
3.89
7.26
12.85
19.19
24.64
27.78
27.79
24.67
19.23
12.89
7.29

error
0.09
-0.09
0.14
-1.30
1.91
-0.14
-0.68
-1.14
1.43
0.67
-1.49
0.61

squared
error
0.01
0.01
0.02
1.69
3.64
0.02
0.47
1.31
2.04
0.45
2.21
0.37

A=

12.44

B=

0.52

h=

-7.72

k=

15.76

Residuals graph

ymeasured

30.00

ypredicted

25.00
20.00
y

average
ymeasured =

15.00
10.00

15.9

SSE =

12.22

5.00
0.00
0

Sinex 2008

10

12

14

This plot should look random!!!


2.50
2.00

residuals

1.50
1.00
0.50
0.00
-0.50 0

-1.00
-1.50
-2.00
x

10

12

14

Logistic Regression
y = c/(1+ae-bx)
x
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ymeasured

ypredicted

201
288
412
574
785
1037
1320
1621
1909
2171
2387
2559
2691
2784
2852
2900

200
289
411
575
784
1036
1322
1620
1910
2170
2388
2560
2690
2785
2852
2899

error
0.999
-0.894
0.509
-1.077
1.001
0.617
-1.560
0.549
-1.016
1.065
-0.741
-0.984
0.939
-0.916
-0.334
0.617

squared
error
0.998
0.800
0.259
1.160
1.001
0.381
2.435
0.301
1.032
1.135
0.549
0.969
0.881
0.838
0.111
0.380

SSE = 13.230198

a = 13.999903
b=

0.4

c = 2999.9984

Residuals graph

ymeasured

3500

ypredicted

3000
2500
y

2000
1500
1000
500
0
0

10

Sinex 2008

12

14

16

This plot should look random!!!


1.500
1.000
residuals

0.500
0.000
-0.500

-1.000
-1.500
-2.000
x

10

12

14

16

Translated Exponential Decay

x
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ymeasured

ypredicted

1102
954
832
735
635
560
500
438
390
354
312
285
260
231
216
200

1100.00
957.27
834.92
730.02
640.10
563.01
496.93
440.28
391.71
350.07
314.38
283.78
257.55
235.06
215.79
199.26

y = yoe-kx + c
error
2.00
-3.27
-2.92
4.98
-5.10
-3.01
3.07
-2.28
-1.71
3.93
-2.38
1.22
2.45
-4.06
0.00
0.74
SSE =

squared
error
4.00
10.70
8.50
24.77
26.02
9.08
9.43
5.18
2.92
15.42
5.67
1.48
5.99
16.52
0.00
0.55
146

yo = 1000.0014
k = 0.1539992
c = 99.997897

1200

Residuals graph

ymeasured

ypredicted

1000
800
y

600
400
200
0
0

10

Sinex 2008

12

14

16

This plot should look random!!!


6.00

residuals

4.00
2.00
0.00
-2.00

-4.00
-6.00
x

10

12

14

16

Notes:
Solver needs to be loaded - go to Tools > Add-Ins and select the Solver Add-In
The data will handle 16 points and the graph is set to graph them. If you have less it has no effect on the graph.
If you have less than 16 points, remove the formulas in Columns C, D, and E. Otherwise it influences the SSE.
If they are needed later, just click and drag them down.
On occasion the Solver will not give a correct solution ( blue line misses red data by a mile!!!), this is why
it helps to set the parameters close to the data points.
The blue line is a smoothed curve using the cubic spline for smoothing, it is NOT a regression line like you get
with the trendline. If you want to predict use the regression equation results.
The error column has conditional formatting to enhance the sign of the error. This helps see bias when manually
fitting the data and shows the random nature of a good proper fit.

Sinex 2008

You might also like