Formula requires contigous cells - work around required

[Magriza]

Hi all,

If a formula requires contiguous cells as part of the argument is there some way of creating a contiguous range from non-contiguous cells without moving data?

For example, if I have data in A1, C1 and E1 and would like to use the values to calculate the trend going forward how can I use the TREND formula so that =TREND(array(A1, C1, E1)) becomes the argument for known y's without moving the data into contiguous cell (eg A1:C1)?

Any ideas appreciated - this one's been bugging me for some time!

 

[Magriza]

Bump (1 of 2)

 

[Magriza]

Bump (2 of 2)

 

[Aladin Akyurek]

Hi all,

If a formula requires contiguous cells as part of the argument is there some way of creating a contiguous range from non-contiguous cells without moving data?

For example, if I have data in A1, C1 and E1 and would like to use the values to calculate the trend going forward how can I use the TREND formula so that =TREND(array(A1, C1, E1)) becomes the argument for known y's without moving the data into contiguous cell (eg A1:C1)?

Any ideas appreciated - this one's been bugging me for some time!

Download and install the free morefunc.xll add-in and invoke:

=TREND(ARRAY.JOIN(A1,C1,E1))

 

[Kenneth Hobson]

Aladin's response should work. I would make a UDF otherwise.

 

[Magriza]

Thanks for the responses.

Aladin - alas my work PC is locked down tighter than <insert comparison here> and so I'm unable to install any additional functions. Also, the workbook will need to be used on other PCs so even if I can get the draconian IT department to install the function on my PC it won't help when I distribute it.

Kenneth - looks like I'll have to explore the UDF route. Maybe in future I'll lay out my data better!

 

[RalphA]

Would this solution work for you?

Example:
a1:a5 = 1,2,3,4,5
b1:b5 = 2,5,6,8,10
Auxiliary column d, with
d1:d5 =a1 copied down
c1 =TREND(d1:B5, A1:A5)

Fpr the trend in rows a1, a3, a5, change the formula in d2 to
=(d1+d3)/2
Copy down a1:a2 to an even row, say a4In
Now, the trend value in c1 is for the values in rows 1, 3, 5 only.

The method can be changed to whatever is needed. Let us know if this solves your problem.

 

[Magriza]

Hi RalphA,

Thanks for the reply however the solution won't work as I've fixed cells. I was after a way to use non-contiguous data in a formula that requires contiguous data. The TREND formula was used as an example (though I was working on TREND at the time). In the end I had to put the data into a contiguous column and then used =TREND(TRANSPOSE(range),{1,2,3},4) entered as an array. Seemed to do the trick.

Ideally Aladin's solution was what I was after - if only Excel supported it natively!

 

[Aladin Akyurek]

Hi RalphA,

Thanks for the reply however the solution won't work as I've fixed cells. I was after a way to use non-contiguous data in a formula that requires contiguous data. The TREND formula was used as an example (though I was working on TREND at the time). In the end I had to put the data into a contiguous column and then used =TREND(TRANSPOSE(range),{1,2,3},4) entered as an array. Seemed to do the trick.

Ideally Aladin's solution was what I was after - if only Excel supported it natively!

Kenneth,

It's not difficult to install it in your home directory in an excel-add-ins folder. For the sake of other users, you can directly include the add-in in the wb you distribute.