Formula requires contigous cells - work around required

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
509
Office Version
  1. 365
Platform
  1. Windows
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! :confused:
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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! :confused:

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

=TREND(ARRAY.JOIN(A1,C1,E1))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top