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

  • Thread starter Thread starter Guest
  • Start date Start date
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?

Thank you in advance.

-Jessy Houle
 
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
 
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.
Click to expand...
...

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...
 
hgrove said:
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
Click to expand...

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...
 
Back
Top