Glad it's all working. Indeed no need to use the Index function at all (but
you had asked about it!).
FWIW, just for fun you could do -
arr = Evaluate("PNR")
Range("F2").Resize(UBound(arr), UBound(arr, 2)).Value = arr
or with the 1D array that's returned with the example I posted
arr = Evaluate("nmLinest")
Range("d16").Resize(, UBound(arr)).Value = arr
refersto: =LINEST(KnownY, KnownX^{1,2})
I just don't understand what it's
calculating for me. :)
The coefficients of a 2-order polynomial, a, b & c
y = ax^2 + bx + c
Now you've got it working don't want to be a killjoy but the formula will
probably calculate faster in cells than with Evaluate !
Regards,
Peter T
wrote in message
...
Thanks, Peter!
(Memo to self - when posting between 1 AM and 8 AM, give lots of
explanation about what I'm asking!)
Using your method, I generated the following cells in a worksheet
A B
1 1 10
2 2 19
3 3 32
4 4 49
5 5 70
6 6 95
7 7 124
8 8 157
9 9 194
10 10 235
To define what I should be expecting from the Linest formula, I
selected the range C1:E10 and in that range entered the array formula
=Linest(KnownY,KnownX,True,True). This gave the following values
A B C D E
1 1 10 25 -39 #N/A
2 2 19 1.788854382 11.09954954 #N/A
3 3 32 0.960651706 16.24807681 #N/A
4 4 49 195.3125 8 #N/A
5 5 70 51562.5 2112 #N/A
6 6 95 #N/A #N/A #N/A
7 7 124 #N/A #N/A #N/A
8 8 157 #N/A #N/A #N/A
9 9 194 #N/A #N/A #N/A
10 10 235 #N/A #N/A #N/A
(Obviously, I made the array formula range too large, but that
shouldn't affect anything below)
Next, I used Insert | Names | Define to define a named range PNR
(Peter's Named Range) and in the Refers to box put the formula used
above =Linest(KnownY,KnownX,True,True)
(I freely admit that your formula puzzled me - too early again,
perhaps - but the KnownX^{1,2} part blew right by me, and it wasn't
accepted when I tried to enter it as part of the array formula for a
worksheet range. Howerver, for my true application, I need the
equivalent of the statistics generated by Linest, so I went ahead.)
In the VBA code, your
Arr =Evaluate("PNR")
was the piece that was I needed - after that, even a plain
Debug.Print Arr(i,n)
would return the same values as seen in the worksheet (for appropriate
values of i and n, of course) !
BTW, I did define a second named range/array formula via Insert |
Names | Define using your expression
refersto: =LINEST(KnownY, KnownX^{1,2})
and it works just like you said - I just don't understand what it's
calculating for me. :)
Again - thanks, Peter!
James
On Apr 27, 5:44 am, "Peter T" <peter_t@discussions wrote:
Hi James,
It's not clear what you are doing, eg have you created a named range or a
named formula or both, how are you using Index, etc.
I did the following -
Created two dynamic named ranges KnownX & KnownY to refer to cells in
columns A & B
Created a named formula
"nmLinest" refersto: =LINEST(KnownY, KnownX^{1,2})
I entered some X's in col-A, FWIW 1, 2 & 3
For Y's, in B1: =A1^2*2+A1*3+5
and filled down
In VBA -
arr = Evaluate("nmLinest")
For i = 1 To UBound(arr) ' to 3
Debug.Print Application.Index(arr, i)
Next
1.99999999999999
3.00000000000004
4.99999999999997
A tad of rounding would return correct results of 2, 3 & 5 in this
contrived
example.
Regards,
Peter T