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?

Thank you in advance.

-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
 
Joined
Feb 14, 2018
Messages
2
Reaction score
0
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...
 
Joined
Feb 14, 2018
Messages
2
Reaction score
0
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top