I need piecewise regression

misterno · May 26, 2016

I can do a regression but it seems like there are 2 slopes here and I do not know how to create 2 equations

I found some VBA codes in the forum for 3 slopes in a regression but I need 2 pieces here.

Thanks

4.11
3.93
3.87
3.63
3.44
3.73
3.90
3.94
3.81
3.66
3.88
3.88
3.46
3.45
3.36
3.38
3.48
3.72
3.48
3.46
3.38
3.34
3.41
3.74
3.53
3.40
3.19
4.20
5.87
3.67
3.67
10.69
11.52
13.47
10.57
4.57
3.36
2.89
3.29
3.53
3.29
3.47
3.17
3.80
6.80
7.40
7.10
5.60
4.03
5.19
5.20
4.32
4.41
3.55
2.92
3.37
3.20
3.01
3.46
3.55
3.63
3.13
2.92
2.70
2.70
2.83
2.79
2.66
2.89
3.23
2.91
2.92
5.41
5.90
5.29
2.88
2.80
2.84
2.78
2.90
3.05
3.05
3.05
2.94
2.88
2.91
2.93
2.93
2.71
2.83
2.70
4.12
3.91
3.60
3.70
3.65
4.34
3.93
3.63
3.67
3.33
3.87
4.15
4.13
3.68
3.59
3.25
3.33
3.42
3.56
3.51
3.62
3.97
3.97
4.26
4.00
4.00
4.31
6.14
11.02
10.54
10.54
16.61
16.61
9.85
6.01
5.00
4.65
4.82
4.36
3.42
3.33
3.26
3.36
3.36
3.27
3.90
4.68
5.80
9.47
14.52
13.81
16.12
17.90
21.88
35.05
36.47
11.09
10.23
9.80
3.92
3.47
3.97
13.66
13.35
12.94
13.35
13.05
10.47
11.53
7.88
21.31
21.31
18.55
5.54
6.39
6.11
5.27
14.11
15.41
15.08
13.78
13.61
17.11
5.92
3.99
4.04
4.14
3.90
3.79
3.85
4.03
3.78
3.83
3.82
3.85
3.96
4.09
4.28
4.09
5.04
8.11
16.75
6.53
7.29
6.81
7.45
6.91
5.01
4.39
4.05
3.65
3.08
3.76
4.64
4.66
4.66
4.55
4.47
4.30
4.75
6.05
14.02
14.65
30.86
15.57
13.78
12.00
56.40
28.64
9.46
4.26
3.71
4.07
4.07
4.36
4.64
4.93
4.87
15.66
15.66
13.54
17.07
123.81
80.76
48.51
58.50
59.10
53.12
91.93
29.18
14.22
6.36
5.73
5.38
5.94
5.95
9.67
20.99
18.09
12.29
12.42
12.42
20.20
20.47
7.78
6.23
8.56
9.54
9.34
8.46
6.04
6.11
6.32
7.44
7.07
8.20
10.24
19.36
24.31
20.94
4.01
3.75
3.55
3.74
3.53
3.23
3.49
3.92
3.73
4.09
4.51
3.73
3.80
3.61
3.61
3.71
3.61
3.72
3.74
3.60
3.60
3.43
2.99
1.63
1.33
1.57
1.51
1.47
1.61
3.47
3.44
3.14
3.14
3.31
2.66
3.24
10.04
18.92
14.30
8.98
11.74
10.80
9.76
3.57
5.00
4.66
6.55
5.74
5.15
4.79
4.85
3.35
3.27
3.26
8.63
8.22
9.44
12.34
9.98
3.64
7.71
8.57
15.14
15.14
8.91
3.78
13.21
6.00
3.51
3.27
3.59
7.34
11.39
15.24
20.00
16.83
18.39
19.26
17.35
37.08
36.35
24.06
21.78
17.46
20.91
22.93
13.72
22.97
16.01
16.68
2.11
2.03
2.19
2.10
1.88
1.91
1.91
2.06
2.01
1.74
1.35
0.95
0.88
0.88
1.39
1.71
1.48
1.09
2.12
2.15
1.94
1.54
0.96
0.98
0.77
0.88
0.89
1.07
1.99
1.97
2.09
2.83
2.90
2.86
3.24
7.05
3.86
3.03
2.61
3.13
2.80
3.80
4.44
7.17
2.85
2.17
5.47
6.43
7.17
7.32
3.99
3.58
3.45
3.94
3.94
3.90
2.67
2.54
2.43
2.31
2.34
2.10
2.29
2.08
1.60
1.79
2.07
2.20
2.13
2.10
2.83
3.46
5.76
5.41
9.14
9.68
8.52
6.56
1.96
2.12
1.98
1.66
1.73
1.83
3.11
1.71
1.78
1.90
1.32
1.07
1.01

43
42
39
44
48
58
52
40
40
37
32
32
36
42
52
49
36
30
36
44
37
37
37
31
36
39
44
38
30
40
47
44
38
24
20
32
40
48
38
32
40
38
48
39
30
22
26
46
40
26
32
26
24
43
48
42
38
51
40
38
36
47
57
42
38
35
36
40
43
34
36
36
32
24
34
42
40
36
44
42
38
38
38
48
54
44
38
38
44
44
40
42
46
52
57
48
34
36
46
45
52
47
39
37
38
38
46
44
50
42
37
46
38
38
32
36
35
38
34
30
34
34
36
29
28
32
34
40
37
36
39
42
40
46
46
48
38
35
40
32
39
43
30
20
14
20
16
18
26
30
42
52
46
26
24
27
24
30
32
29
36
28
28
40
39
35
38
42
34
26
28
36
31
30
30
40
39
37
36
45
42
39
44
45
45
54
51
34
30
34
32
26
26
31
30
36
28
28
29
34
44
56
66
54
35
26
32
36
41
44
36
30
32
28
14
16
28
40
8
18
29
32
50
43
43
47
44
34
34
32
32
44
24
10
14
14
23
22
32
16
16
18
34
39
44
36
28
34
26
29
27
26
26
21
17
30
37
34
24
26
35
36
42
48
42
46
34
30
27
25
18
54
40
44
40
39
46
40
30
41
38
35
38
38
42
42
42
46
38
36
34
34
38
48
56
54
42
45
47
40
32
28
34
36
34
51
36
21
18
14
28
20
28
37
28
26
32
34
24
34
40
39
30
36
29
36
40
28
27
26
26
31
21
31
29
24
35
30
24
34
40
32
36
32
32
16
24
15
10
21
22
16
10
20
40
24
16
26
28
26
22
50
56
48
44
43
42
42
44
45
52
48
58
62
62
60
50
52
46
37
36
44
58
62
67
64
56
55
42
44
48
46
38
36
37
26
21
28
34
39
46
54
30
32
24
34
40
47
34
24
22
29
32
24
27
27
28
34
37
32
34
33
42
52
42
50
52
36
33
36
37
34
32
22
20
18
12
25
47
38
32
30
51
48
44
40
54
51
36
34
50
54

kalak · May 26, 2016

misterno said:
I can do a regression but it seems like there are 2 slopes here and I do not know how to create 2 equations

I found some VBA codes in the forum for 3 slopes in a regression but I need 2 pieces here.
...
...
Thanks

Putting your data into a chart it looks like a nonlinear relationship.
Like instead of
Y = a + b*X
it's more like
Y = a + b/X

The latter implies a continuously varying slope.

Why do you want just two slopes?

This isn't an idle question, because with regressions the more information you put into them at the start the better the result is likely to be.

If you insist on two slopes, are you looking at two totally independent (linear?) regressions, or something like a spline regression joined at one (or more) spline knots, or maybe something else?

If you want two regressions, are you thinking of (say) the first third of your data list being used for one and the remaining two-thirds being used for the other? You can estimate the split point using a best fit or other criterion if needed. Or why not just use an arbitrary split point, if you've got a reason not to do so then what is that reason?

Or is it that you're looking for some kind of discrimination or classification function picking out which of your dataset best fits one line and which of it best fits the other?

You should really provide more information to get a reasonable response.

misterno · May 26, 2016

kalak said:
Putting your data into a chart it looks like a nonlinear relationship.
Like instead of
Y = a + b*X
it's more like
Y = a + b/X

The latter implies a continuously varying slope.

Why do you want just two slopes?

This isn't an idle question, because with regressions the more information you put into them at the start the better the result is likely to be.

If you insist on two slopes, are you looking at two totally independent (linear?) regressions, or something like a spline regression joined at one (or more) spline knots, or maybe something else?

If you want two regressions, are you thinking of (say) the first third of your data list being used for one and the remaining two-thirds being used for the other? You can estimate the split point using a best fit or other criterion if needed. Or why not just use an arbitrary split point, if you've got a reason not to do so then what is that reason?

Or is it that you're looking for some kind of discrimination or classification function picking out which of your dataset best fits one line and which of it best fits the other?

You should really provide more information to get a reasonable response.

well I want 2 linear equations because that is the requirement by the management

The data is designed such a way that you will have 2 distinct equations anyway

kalak · May 26, 2016

misterno said:
well I want 2 linear equations because that is the requirement by the management.

The data is designed such a way that you will have 2 distinct equations anyway

OK. So just split the data according to the design and run a separate regression on each split.

There's a couple of handy regressions in Excel, LINEST and the one in the analysis pac, or you can use a made up one of which there's a few around.

misterno · May 26, 2016

kalak said:
OK. So just split the data according to the design and run a separate regression on each split.

There's a couple of handy regressions in Excel, LINEST and the one in the analysis pac, or you can use a made up one of which there's a few around.

That is the problem

I want the algorith or VBA code to determine where the cut off is where the other equation/slope starts

Otherwise mine would be a guess work

kalak · May 27, 2016

misterno said:
That is the problem

I want the algorith or VBA code to determine where the cut off is where the other equation/slope starts

Otherwise mine would be a guess work

So you want to divide the data up into say the top third and the bottom 2/3 with the split point determined by the data?

You can do a "brute force" approach in that case by doing one regression on say the first three sample point, and another on the remainder.

Then on the first four, and on the remainder and so on. All that wouldn't take long with modern computers. And then use some criterion such as combined correlations to choose the regression pair giving best overall fit.

But this seems rather pointless without some clearer knowledge of how your data are generated. For example the result might change if the data were sorted differently.
Or maybe you want to pick out every second sample value for the first regression and the in between ones for the second. Who knows?

And do you want totally independent regressions or ones that are splined or joined in some way. Who knows?

You opening post indicates you know how to do what you want with 3 regressions. So why don't you just change the 3 to 2 in that procedure, whatever it is?

misterno · May 27, 2016

kalak said:
So you want to divide the data up into say the top third and the bottom 2/3 with the split point determined by the data?

You can do a "brute force" approach in that case by doing one regression on say the first three sample point, and another on the remainder.

Then on the first four, and on the remainder and so on. All that wouldn't take long with modern computers. And then use some criterion such as combined correlations to choose the regression pair giving best overall fit.

But this seems rather pointless without some clearer knowledge of how your data are generated. For example the result might change if the data were sorted differently.
Or maybe you want to pick out every second sample value for the first regression and the in between ones for the second. Who knows?

And do you want totally independent regressions or ones that are splined or joined in some way. Who knows?

You opening post indicates you know how to do what you want with 3 regressions. So why don't you just change the 3 to 2 in that procedure, whatever it is?

The regression should not change no matter how you sort

There are some VBA codes out there for 3 equations but nothing for 2, and I was not able to adjust it for 2 equations

Maybe you can help me with the VBA code below?

http://www.mrexcel.com/forum/excel-...ear-regression-visual-basic-applications.html

misterno · May 27, 2016

Can someone please help me?

AhoyNC · May 27, 2016

You might try posting your question on a statistic forum.
Here are a couple.

Statistics Help @ Talk Stats Forum
Statistics Forum - Statistics Help

kalak · May 27, 2016

misterno said:
The regression should not change no matter how you sort

There are some VBA codes out there for 3 equations but nothing for 2, and I was not able to adjust it for 2 equations

Maybe you can help me with the VBA code below?

http://www.mrexcel.com/forum/excel-...ear-regression-visual-basic-applications.html

That link is about multiple regression, where you have:
Y = a + bx + cz
sort of thing rather than the
Y= a + bx
setup that you suggested in your opening post.

Multiple regression is easy enough to do, but if you could be more specific about just what you want done ...

Which particular post in that link do you consider relevant to your problem? And how do your posted data relate to it?

I need piecewise regression

misterno

Board Regular

Excel Facts

kalak

Active Member

misterno

Board Regular

kalak

Active Member

misterno

Board Regular

kalak

Active Member

misterno

Board Regular

misterno

Board Regular

AhoyNC

Well-known Member

kalak

Active Member

Similar threads

Forum statistics

Share this page

I need piecewise regression

Board Regular

Excel Facts

Active Member

Board Regular

Active Member

Board Regular

Active Member

Board Regular

Board Regular

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock