# Formula requires contigous cells - work around required

#### Magriza

##### Well-known Member
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!

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Bump (1 of 2)

#### Magriza

Bump (2 of 2)

##### MrExcel MVP
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!

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

#### Kenneth Hobson

##### Well-known Member
Aladin's response should work. I would make a UDF otherwise.

#### Magriza

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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!

##### MrExcel MVP
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.

1,101,805
Messages
5,482,994
Members
407,371
Latest member
Ernest F Mink

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...