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>

 

[DILIPandey]

Okay. I noticed a change in your formula:-
Your formula ended with ;1)
but my formula ended with ,2)

I am sure that any dynamic range can be defined using Name Manager... if you are stuck, share your file like I did.

Regards,
DILIPandey

 

[Sophhie]

true, i changed trying to define the range only for column B. and also chaged the reference cell to B$1$ ...

ok will share the files next time

best regards

 

[Sophhie]

Hi DILIPandey , thanks! now i can do what i wanted in the first place. would only like you to explain me this part of the formula (the part in Bold) specially the -1:

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

thanks

 

[DILIPandey]

Hi Sophhie,

IF condition is returning the row numbers where column B is not blank, so with MIN formula, it will return the minimum row number which has the data in column B.
Now, Range will be offset (start) from that row onward.
minus one is there to handle the start position of range. If we remove -1, then range will start from second row of column B which has the data.

Regards,
DILIPandey

 

[Jon Peltier]

Excel is pretty smart. I pasted all of your data into a worksheet, starting in A2 (labels in A1 and B1).

This formula, which used all the data including blanks:

=SLOPE(B2:B800,A2:A800)

gave me the same result as this formula, which only includes rows with values in column B:

=SLOPE(B479:B566,A479:A566)

 

[Sophhie]

Thank you both very much for your answers. Now i can do what i wanted in the first place and in a simple way.

Thanks
Best Regards,
*

 

[DILIPandey]

you are welcome Sophhie... cheers


Regards,
DILIPandey