Formula requires contigous cells - work around required

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
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:
 

Some videos you may like

Excel Facts

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,179
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))
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Aladin's response should work. I would make a UDF otherwise.
 

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
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
Joined
May 14, 2003
Messages
3,829
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
Joined
Nov 16, 2005
Messages
508
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,179
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top