# Is it possible to use Excel's SLOPE function on a non-continuous r

G

#### Guest

Is it possible to use Excel's SLOPE function on a non-continuous range?

Slope works fine if you have data in A1:C1 that you want to compare to A2:C2 ( =SLOPE(A1:C1,A2:C2) ). However, as soon as you want to compare A1, C1 & E1 to A2:C2, Excel will not accept the formula.

Has anyone found a workaround for this?

-Jessy Houle

T

#### Tushar Mehta

No, I believe you have to have data in a contiguous range. Of course,
you could always create a secondary area, say A3:C3 that is contiguous
with formulas like =A1, =C1, and =E1

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

H

#### hgrove

jesse james wrote...
...
Slope works fine if you have data in A1:C1 that you want to
compare to A2:C2 ( =SLOPE(A1:C1,A2:C2) ). However, as soon
as you want to compare A1, C1 & E1 to A2:C2, Excel will not
accept the formula.
...

The general approach to converting multiple area ranges into a singl
array involves either INDIRECT or OFFSET.

=SLOPE(N(INDIRECT({"A1","C1","E1"})),A2:C2)

=SLOPE(N(OFFSET(A1,0,{0,2,4},1,1)),A2:C2)

The N() calls are *NOT* optional. INDIRECT and OFFSET when fed arra
first arguments return what appear to be arrays of range references. I
you enter their results into multiple cell ranges, Excel displays th
values in those ranges. However, you can't use their results directl
as intermediate array values in other expressions. Wrapping them insic
N() converts the array of range references to an array of the numeri
values of the top-left cell in each of the range references

#### NMAN

Hgrove: Your solution worked. Thank you. I would like to extend it for large range of non contiguous cells. For e.g. slope ((A2:N2,Q2:AA2),(A3:N3,Q3:AA3)). If I have to use the indirect formula above I will have to enter each cell name individually which will become impossible if I have several tens of cells. Is there a way by which above method can be adapted to the case where there are large number of cells ? Thanks...

#### NMAN

jesse james wrote...
...
...

The general approach to converting multiple area ranges into a singl
array involves either INDIRECT or OFFSET.

=SLOPE(N(INDIRECT({"A1","C1","E1"})),A2:C2)

=SLOPE(N(OFFSET(A1,0,{0,2,4},1,1)),A2:C2)

The N() calls are *NOT* optional. INDIRECT and OFFSET when fed arra
first arguments return what appear to be arrays of range references. I
you enter their results into multiple cell ranges, Excel displays th
values in those ranges. However, you can't use their results directl
as intermediate array values in other expressions. Wrapping them insic
N() converts the array of range references to an array of the numeri
values of the top-left cell in each of the range references

Hgrove: Your solution worked. Thank you. I would like to extend it for large range of non contiguous cells. For e.g. slope ((A2:N2,Q2:AA2),(A3:N3,Q3:AA3)). If I have to use the indirect formula above I will have to enter each cell name individually which will become impossible if I have several tens of cells. Is there a way by which above method can be adapted to the case where there are large number of cells ? Thanks...