CODEWITHSUNDEEP
arraysexcel
user3036843
user3036843

Reputation: 77

Is it possible to use individual cell references in an Excel array within a function?

I have some data I want to put into the LOGEST() function

(e.g. x values =0.463, 0.609, 0.887, y values = 0.05, 0.1, 0.2 )

For this example I have put the data into these columns: enter image description here Both these formula work (return the correct value of 0.665...):

= INDEX( LOGEST({0.95;0.9;0.8}, {0.463;0.609;0.887}, TRUE, FALSE), 1)

and

= INDEX( LOGEST( B2:B4, A2:A4, TRUE, FALSE), 1)

But I cannot get the formula to call individual cells in the array. I have tried a few things e.g. 

= INDEX( LOGEST({0.95,0.9,0.8}, {"A2","A3","A4"}, TRUE, FALSE), 1)
= INDEX( LOGEST({0.95,0.9,0.8}, (A2, A3, A4), TRUE, FALSE), 1)

And some other variations, but I cannot get excel to treat individual cell values as array values. Is there a way to do this or is the only constant cell referencing possible the A2:A4? I ask because in my real data I can't use this range function as my data is not distributed in that form (there are gaps). (Note: In case it is relevant ultimately I don't want the cell values to remain fixed - they should move downwards when I drag expand downwards).

Upvotes: 3

Views: 8152

Answers (1)

Domenic
Domenic

Reputation: 8104

You can use the CHOOSE function to return the array...

= INDEX( LOGEST({0.95;0.9;0.8}, CHOOSE({1;2;3},A2,A3,A4), TRUE, FALSE), 1)

Hope this helps!

Upvotes: 3

Related Questions