Automatically get the x and y values to calculate a SLOPE

[Sophhie]

Hi, i would like to calculate a slope, being the x values in Column A and y values in Columns B,C,D.. below is an example with only columns A and B.
I would like the function SLOPE to pick up the y values in columns B,C,D.. (which means to pick up the range of cells that is not blank) and to pick the corresponding x values in column A.

or, in alternative, to manually pick the y values that are not blank and then, Excel automatically picks the corresponding values of x (the values of x have the same range of rows as the values of y but are in another Column)

thanks

~

A
12,00
11,98
11,97
11,95
11,94
11,92
11,91
11,89
11,88
11,86
11,85
11,83
11,82
11,80
11,79
11,77
11,76
11,74
11,73
11,71
11,70
11,68
11,67
11,65
11,64
11,62
11,61
11,59
11,58
11,56
11,55
11,53
11,52
11,50
11,49
11,47
11,46
11,44
11,43
11,41
11,40
11,38
11,37
11,35
11,34
11,32
11,31
11,29
11,28
11,26
11,25
11,23
11,22
11,20
11,19
11,17
11,16
11,14
11,13
11,11
11,10
11,08
11,07
11,05
11,04
11,02
11,01
10,99
10,98
10,96
10,95
10,93
10,92
10,90
10,89
10,87
10,86
10,84
10,83
10,81
10,80
10,78
10,77
10,75
10,74
10,72
10,71
10,69
10,68
10,66
10,65
10,63
10,62
10,60
10,59
10,57
10,56
10,54
10,53
10,51
10,50
10,48
10,47
10,45
10,44
10,42
10,41
10,39
10,38
10,36
10,35
10,33
10,32
10,30
10,29
10,27
10,26
10,24
10,23
10,21
10,20
10,18
10,17
10,15
10,14
10,12
10,11
10,09
10,08
10,06
10,05
10,03
10,02
10,00
9,99
9,97
9,96
9,94
9,93
9,91
9,90
9,88
9,87
9,85
9,84
9,82
9,81
9,79
9,78
9,76
9,75
9,73
9,72
9,70
9,69
9,67
9,66
9,64
9,63
9,61
9,60
9,58
9,57
9,55
9,54
9,52
9,51
9,49
9,48
9,46
9,45
9,43
9,42
9,40
9,39
9,37
9,36
9,34
9,33
9,31
9,30
9,28
9,27
9,25
9,24
9,22
9,21
9,19
9,18
9,16
9,15
9,13
9,12
9,10
9,09
9,07
9,06
9,04
9,03
9,01
9,00
8,98
8,97
8,95
8,94
8,92
8,91
8,89
8,88
8,86
8,85
8,83
8,82
8,80
8,79
8,77
8,76
8,74
8,73
8,71
8,70
8,68
8,67
8,65
8,64
8,62
8,61
8,59
8,58
8,56
8,55
8,53
8,52
8,50
8,49
8,47
8,46
8,44
8,43
8,41
8,40
8,38
8,37
8,35
8,34
8,32
8,31
8,29
8,28
8,26
8,25
8,23
8,22
8,20
8,19
8,17
8,16
8,14
8,13
8,11
8,10
8,08
8,07
8,05
8,04
8,02
8,01
7,99
7,97
7,96
7,94
7,93
7,91
7,90
7,88
7,87
7,85
7,84
7,82
7,81
7,79
7,78
7,76
7,75
7,73
7,72
7,70
7,69
7,67
7,66
7,64
7,63
7,61
7,60
7,58
7,57
7,55
7,54
7,52
7,51
7,49
7,48
7,46
7,45
7,43
7,42
7,40
7,39
7,37
7,36
7,34
7,33
7,31
7,30
7,28
7,27
7,25
7,24
7,22
7,21
7,19
7,18
7,16
7,15
7,13
7,12
7,10
7,09
7,07
7,06
7,04
7,03
7,01
7,00
6,98
6,97
6,95
6,94
6,92
6,91
6,89
6,88
6,86
6,85
6,83
6,82
6,80
6,79
6,77
6,76
6,74
6,73
6,71
6,70
6,68
6,67
6,65
6,64
6,62
6,61
6,59
6,58
6,56
6,55
6,53
6,52
6,50
6,49
6,47
6,46
6,44
6,43
6,41
6,40
6,38
6,37
6,35
6,34
6,32
6,31
6,29
6,28
6,26
6,25
6,23
6,22
6,20
6,19
6,17
6,16
6,14
6,13
6,11
6,10
6,08
6,07
6,05
6,04
6,02
6,01
5,99
5,98
5,96
5,95
5,93
5,92
5,90
5,89
5,87
5,86
5,84
5,83
5,81
5,80
5,78
5,77
5,75
5,74
5,72
5,71
5,69
5,68
5,66
5,65
5,63
5,62
5,60
5,59
5,57
5,56
5,54
5,53
5,51
5,50
5,48
5,47
5,45
5,44
5,42
5,41
5,39
5,38
5,36
5,35
5,33
5,32
5,30
5,29
5,27
5,26
5,24
5,23
5,21
5,20
5,18
5,17
5,15
5,14
5,12
5,11
5,09
5,08
5,06
5,05
5,03
5,02
5,00
4,99
4,97
4,96
4,94
4,93
4,91
4,90
4,88
4,87
4,85
4,84	255,3143
4,82	254,9056
4,81	254,4951
4,79	254,0828
4,78	253,6688
4,76	253,2531
4,75	252,8357
4,73	252,4166
4,72	251,9959
4,70	251,5736
4,69	251,1498
4,67	250,7244
4,66	250,2975
4,64	249,8692
4,63	249,4394
4,61	249,0082
4,60	248,5757
4,58	248,1419
4,57	247,7067
4,55	247,2703
4,54	246,8327
4,52	246,3938
4,51	245,9539
4,49	245,5128
4,48	245,0707
4,46	244,6275
4,45	244,1833
4,43	243,7382
4,42	243,2922
4,40	242,8453
4,39	242,3977
4,37	241,9492
4,36	241,5
4,34	241,0502
4,33	240,5998
4,31	240,1487
4,30	239,6972
4,28	239,2452
4,27	238,7928
4,25	238,34
4,24	237,887
4,22	237,4337
4,21	236,9802
4,19	236,5266
4,18	236,0729
4,16	235,6193
4,15	235,1656
4,13	234,7121
4,12	234,2587
4,10	233,8055
4,09	233,3526
4,07	232,9
4,06	232,4478
4,04	231,996
4,03	231,5447
4,01	231,0939
3,99	230,6437
3,98	230,1941
3,96	229,7451
3,95	229,2969
3,93	228,8495
3,92	228,4028
3,90	227,9571
3,89	227,5122
3,87	227,0682
3,86	226,6252
3,84	226,1833
3,83	225,7424
3,81	225,3025
3,80	224,8639
3,78	224,4264
3,77	223,9901
3,75	223,5551
3,74	223,1214
3,72	222,689
3,71	222,258
3,69	221,8284
3,68	221,4002
3,66	220,9735
3,65	220,5483
3,63	220,1247
3,62	219,7027
3,60	219,2822
3,59	218,8635
3,57	218,4464
3,56	218,031
3,54	217,6174
3,53	217,2056
3,51
3,50
3,48
3,47
3,45
3,44
3,42
3,41
3,39
3,38
3,36
3,35
3,33
3,32
3,30
3,29
3,27
3,26
3,24
3,23
3,21
3,20
3,18
3,17
3,15
3,14
3,12
3,11
3,09
3,08
3,06
3,05
3,03
3,02
3,00
2,99
2,97
2,96
2,94
2,93
2,91
2,90
2,88
2,87
2,85
2,84
2,82
2,81
2,79
2,78
2,76
2,75
2,73
2,72
2,70
2,69
2,67
2,66
2,64
2,63
2,61
2,60
2,58
2,57
2,55
2,54
2,52
2,51
2,49
2,48
2,46
2,45
2,43
2,42
2,40
2,39
2,37
2,36
2,34
2,33
2,31
2,30
2,28
2,27
2,25
2,24
2,22
2,21
2,19
2,18
2,16
2,15
2,13
2,12
2,10
2,09
2,07
2,06
2,04
2,03
2,01
2,00
1,98
1,97
1,95
1,94
1,92
1,91
1,89
1,88
1,86
1,85
1,83
1,82
1,80
1,79
1,77
1,76
1,74
1,73
1,71
1,70
1,68
1,67
1,65
1,64
1,62
1,61
1,59
1,58
1,56
1,55
1,53
1,52
1,50
1,49
1,47
1,46
1,44
1,43
1,41
1,40
1,38
1,37
1,35
1,34
1,32
1,31
1,29
1,28
1,26
1,25
1,23
1,22
1,20
1,19
1,17
1,16
1,14
1,13
1,11
1,10
1,08
1,07
1,05
1,04
1,02
1,01
0,99
0,98
0,96
0,95
0,93
0,92
0,90
0,89
0,87
0,86
0,84
0,83
0,81
0,80
0,78
0,77
0,75
0,74
0,72
0,71
0,69
0,68
0,66
0,65
0,63
0,62
0,60
0,59
0,57
0,56
0,54
0,53
0,51
0,50
0,48
0,47
0,45
0,44
0,42
0,41
0,39
0,38
0,36
0,35
0,33
0,32
0,30
0,29
0,27
0,26
0,24
0,23
0,21
0,20
0,18
0,17
0,15
0,14
0,12
0,11
0,09
0,08
0,06
0,05
0,03
0,02

<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <tbody>
</tbody>

 

[desert_dweller5]

1st it would be easiest to put all your x values in one column and all your y values in the other column. you could use a formula to figure out the slope of each. unless there is more than one x for each y. if the y are equal just use a formula. if they are not then use a VBA script because you have to calculate each x for it's corresponding y or "y"s.

it would go something llike this
This is pseudo-code so don't try to run it
Sub slope()
dim y as long
dim x as long
range("A1").Select
do until activecell.value = ""
x = activecell.value
activecell.offset () !go to the first y value
y = activecell.value
(slope formula goes here)
activecell.offset () !go to the second y value

loop
end sub

 

[DILIPandey]

Okay... so do you want Slope function to consider only below range ?

4,84	255,3143
4,82	254,9056
4,81	254,4951
4,79	254,0828
4,78	253,6688
4,76	253,2531
4,75	252,8357
4,73	252,4166
4,72	251,9959
4,70	251,5736
4,69	251,1498
4,67	250,7244
4,66	250,2975
4,64	249,8692
4,63	249,4394
4,61	249,0082
4,60	248,5757
4,58	248,1419
4,57	247,7067
4,55	247,2703
4,54	246,8327
4,52	246,3938
4,51	245,9539
4,49	245,5128
4,48	245,0707
4,46	244,6275
4,45	244,1833
4,43	243,7382
4,42	243,2922
4,40	242,8453
4,39	242,3977
4,37	241,9492
4,36	241,5
4,34	241,0502
4,33	240,5998
4,31	240,1487
4,30	239,6972
4,28	239,2452
4,27	238,7928
4,25	238,34
4,24	237,887
4,22	237,4337
4,21	236,9802
4,19	236,5266
4,18	236,0729
4,16	235,6193
4,15	235,1656
4,13	234,7121
4,12	234,2587
4,10	233,8055
4,09	233,3526
4,07	232,9
4,06	232,4478
4,04	231,996
4,03	231,5447
4,01	231,0939
3,99	230,6437
3,98	230,1941
3,96	229,7451
3,95	229,2969
3,93	228,8495
3,92	228,4028
3,90	227,9571
3,89	227,5122
3,87	227,0682
3,86	226,6252
3,84	226,1833
3,83	225,7424
3,81	225,3025
3,80	224,8639
3,78	224,4264
3,77	223,9901
3,75	223,5551
3,74	223,1214
3,72	222,689
3,71	222,258
3,69	221,8284
3,68	221,4002
3,66	220,9735
3,65	220,5483
3,63	220,1247
3,62	219,7027
3,60	219,2822
3,59	218,8635
3,57	218,4464
3,56	218,031
3,54	217,6174
3,53	217,2056

<tbody>
</tbody>

Regards,
DILIPandey

 

[Sophhie]

Hi, thanks for your answers. Yes I only want to consider that range of y values and x values.

What I was doing now, was to name the y range from the Columns B, (C,D..etc) and then calculate the slope by

=SLOPE(named_range_of yy values; OFFSET( --->"not written as the formula" did the offset of the range of yy values to the x values of Colunm A).

So, it's a better solution than picking the y and x values manually, but still I have to name the yy values ranges (which will apear in several Coluns B,C,D and so on...)

thanks

 

[DILIPandey]

Hi Sophhie,

With sample data in column A and B, use below formula in name manager to get the required range :-

=OFFSET(Sheet1!$A$1,MIN(IF(Sheet1!B:B<>"",ROW(Sheet1!B:B),""))-1,0,COUNTA(Sheet1!$B:$B),2)


Regards,
DILIPandey

 

[Sophhie]

Hi, sorry but that formula didn't pick what i wanted or i didn't figure how to use... i have to work with all the numbers in several columns, as posted in the first post, so i think due to the blank cells or alternatively cells with "", can't pick just the range of cells i want because function Offset doesn´t work when it meets blank cells or cells with "" (at least from what i read online)...
so i guess it's not possible to automatically pick a range of cells that doesn't start in the first cells of the Sheet...

 

[DILIPandey]

Hi Sophhie,

I have checked the formula before sharing and it worked very well.
After pasting the formula in Name manager, you can click on range selection button on the right side of formula field and it will show you the respective range.

Regards,
DILIPandey

 

[Sophhie]

hi, sorry but when i click on the range selection button it doens't show the range, only a larger or smaller Name Manager box..

best regards

 

[DILIPandey]

Okay.. see below sample file:-

https://dl.dropboxusercontent.com/u/65227334/Dynamic Range.xlsx

After opening above spreadsheet, Press ctrl + F2 to bring name manager and see the defined name "data" which is having your desired range.


Regards,
DILIPandey

 

[Sophhie]

Thanks, i tried out the spreadsheet you sent and it works fine.

But, what we need to calculate the slope is the automatic range done in column B. So how can this formula be applied only to pick that range of values in column B, C,D...?

i tried out this one but the range is not limited on the bottom so can't calculate the slope with it

=DESLOCAMENTO(Sheet1!$B$1;MÍNIMO(SE(Sheet1!$B:$B<>"";LIN(Sheet1!B:B);""))-1;0;CONTAR.VAL(Sheet1!$B:$B);1)

best regards,