0
I have a formula in excel which contains an array; {1,2,3} its use can be seen at I would like to generate it dynamically eg arrayfun(4) would generate {1,2,3,4}. I can generate a very long array with COLUMN(1:1) but I need a formula which generates an array of explicit length.
The specific formula I'm trying to use this in is LINEST and an example of its use can be found at http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
Mr Purple
Posted 2015-02-23T21:08:32.773
Reputation: 305
1
You have the right idea with COLUMN, but perhaps easier to set up would be to use ROW. For your specific example, you could use the following to generate the array {1,2,3,4}.
=ROW(1:4)
Excellll
Posted 2015-02-23T21:08:32.773
Reputation: 11 857
Thanks, Fixing the number of columns did work as in Column(A:C) worked, but switching to rows didnt. presumably because of its transposed nature. So I guess the solution is to either transpose the rows solution or to use indirect or some such to allow for dynamic modification of column(A:C) – Mr Purple – 2015-02-23T21:34:41.407
As per my comment above useing index to generate the column reference with column to generate the array did it. So I will accept this answer and post the specific solution into my question. – Mr Purple – 2015-02-23T21:42:15.657
@MrPurple If you found a solution of your own, you should post it as an answer, not as an edit to your question. (Yes, you may answer your own question.) If that is the solution that worked for you, you should mark your own answer as correct as well. – Excellll – 2015-02-23T22:26:21.727
1
The answer as per help from @Excellll was the formula
=COLUMN(INDEX(1:1024,1,1):INDEX(1:1024,1,3))
or
=TRANSPOSE(ROW(1:3))
Which can be easily made dynamic.
Now I can use LINEST to generate polynomical coefficients with a dynamic order using:
=LINEST(known_ys,known_xs^COLUMN(INDEX(1:1024,1,1):INDEX(1:1024,1,order)),TRUE,TRUE)
or
=LINEST(known_ys,known_xs^TRANSPOSE(ROW(1:order)),TRUE,TRUE)
Where 'known_ys' is a range containing my y values, 'known_xs' is a range containing my x values and 'order' is the order to which I want to apply my polynomial fit.
Furthermore I can wrap the LINEST in an INDEX function to extract any specific coefficient as desired eg:
=INDEX(LINEST(known_ys,known_xs^TRANSPOSE(ROW(1:order)),TRUE,TRUE),1,3)
To return the R² only
Mr Purple
Posted 2015-02-23T21:08:32.773
Reputation: 305
So you want to take an existing array and append new elements to the end of it as a formula result? – Nate – 2015-02-23T21:16:03.163
No, I want to be able to dynamically generate the {1,2,3} as required in the formula to which I have linked. – Mr Purple – 2015-02-23T21:37:02.060