How to use dynamic ranges in array formula - i.e. TREND()

[emte69]

Chandoo,


I followed you now for years and I appreciate all of your techniques, tipps, and advises very much.

Recently I stumbled over the question "How to use dynamic ranges in array formulas?"


I goggled a lot in the net and found much info for the keywords "excel", "dynamic", "range", "array" - but all solutions were special to certain functions.


Well, I have a special question too ;-)

I defined several dyn ranges:

DynDate = OFFSET(dta!$A$3;0;0;COUNTA(dta!$A:$A)-1;1)

DynOldValues = OFFSET(dta!$B$3;0;0;COUNTA(dta!$B:$B)-1;1)

DynNewDates = OFFSET(dta!$C$3;0;0;ANZAHL2(dta!$C:$C)-1;1)


I want to do some estimations with the TREND()-fctn on basis of the old values for the new dates:


=TREND(<Y_range>;<X_range>;<X_new>;WAHR)


with explicite ranges like =TREND(B3:B15;A3:A15;H16:H38;WAHR) everything is fine; but using the dyn ranges above like


=TREND(DynOldValues;DynDates;DynNewValues;TRUE)


spoils my sheet.

Any idea how to use dyn ranges in TREND()?


But more, I'am interested in using dyn ranges with array formulas in general. Is there a general tutorial available?


Cheers and keep up with your fanastic enthusiam to "become us awsome in Excel" ;-)

Best Michael

 

[Hui]

Michael


Have you read http://chandoo.org/wp/2013/02/08/formula-forensics-no-033/


Are you able to post a sample file for us to see

 

[SirJB7]

Hi, emte69!


First of all welcome to Chandoo's website Excel forums. Officially at least, as you've been here for more time than many of us. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Despite of this search, give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/How%20to%20use%20dynamic%20ranges%20in%20array%20formula%20-%20i.e.%20TREND%28%29%20%28for%20emte69%20at%20chandoo.org%29.xlsx


It's just a scratch worksheet that I was using to simulate blocks layouts of squared shapes on a sheet of paper (for PowerPoint purposes). I took columns F, H, I, from row 5 in advance, defined 3 named ranges, set a TREND formula in K5 and it worked fine:

K5: =TENDENCIA(DynOld;DynX;DynNew;VERDADERO) -----> in english: =TREND(DynOld,DynX,DynNew,TRUE)


Hope it helps.


Regarding array formulas here you have the kick-off:

https://www.google.com/url?q=http://chandoo.org/wp/tag/array-formulas/&sa=U&ei=J_eCUYnTC4btrQGroICoBA&ved=0CAcQFjAA&client=internal-uds-cse&usg=AFQjCNEL-00Y746F65mA9YLg21fsgUtOdw


Regards!

 

[emte69]

Hi SirJB7,

thanks for the fast answer; your squares example gave me a hint how to solve my problem without array usage of TREND(). - I'am doing a kind of burn-down-chart with estimating the future from given data. However in your example TREND() is used as a non-array fctn.

Best Michael

 

[SirJB7]

Hi, emte69!

You could try building any list or table and write any array formula using cell references. Once you get it working, define named ranges for the ranges involved and replace within the array formulas their references by their assigned named ranges. If ok, then change the named ranges definition from fixed references to dynamic references and check everything is correct.

Regards!

 

[Hui]

Michael


One comment on the initial post if I may


I have found that when using Named formulas which you want to be dynamic try to use a common field to delimit the length


So instead of:

[pre]

DynDate = OFFSET(dta!$A$3;0;0;COUNTA(dta!$A:$A)-1;1)
DynOldValues = OFFSET(dta!$B$3;0;0;COUNTA(dta!$B:$B)-1;1)
DynNewDates = OFFSET(dta!$C$3;0;0;ANZAHL2(dta!$C:$C)-1;1)
I would use:

DynDate = OFFSET(dta!$A$3;0;0;COUNTA(dta!$A:$A)-1;1)
DynOldValues = OFFSET(dta!$B$3;0;0;COUNTA(dta!$A:$A)-1;1)
DynNewDates = OFFSET(dta!$C$3;0;0;COUNTA(dta!$A:$A)-1;1)

[/pre]
This way the Names are always the same length based on the date field