Array reference in Formula

[nightcrawler23]

HI,
i have the below formula which is working fine.

=MMULT(MINVERSE({1,1,1;4,2,1;9,3,1}),L7:L9)

how do i change the array part to links to cells. Excel isnt allowing me to do so saying "You cannot change part of an array.

This is what i want

=MMULT(MINVERSE({A1^2,B1,1;A2^2,B2,1;A3^2,B3,1}),L7:L9)

 

[Andrew Poulsom]

Did you select all the cells that contain your array formula (Edit|Go To|Special|Current array)?

 

[nightcrawler23]

yes, i did select all the cell --> F2 --> select "1" in the array and click on cell A1 --> ERROR.

My formula works fine when the array is constant. I want to link the array element to cells on my sheet.

 

[Andrew Poulsom]

You need to select all the cells that contain your array formula. You can't edit a single cell in an array.

 

[nightcrawler23]

i do select all the cells of the formula.
my current formula(Which is working fine is this:
=MMULT(MINVERSE({1,1,1;4,2,1;9,3,1}),B1:B3)

here i am inversing an array which is constant. i want to link each element of this array to cells on my sheet like this.

=MMULT(MINVERSE({A1^2,B1,1;A2^2,B2,1;A3^2,B3,1}),L7:L9)

 

[Andrew Poulsom]

That array formula is in more than one cell. Normally it would be in a range of cells with the same number of rows as array1 and the same number of columns as array2. To select all the cells that contain the array formula press Ctrl+/.

 

[barry houdini]

You can't use cell references in an array constant but try using CHOOSE to create the required array - this should work for your example

=MMULT(MINVERSE(CHOOSE({1,2,3},A1:A3^2,B1:B3,B1:B3^0)),L7:L9)

 

[nightcrawler23]

ray: Thanks a lot barry.

That worked exactly ho i wanted.

=MMULT(MINVERSE(CHOOSE({1,2,3},A1:A3^2,A1:A3,A1:A3^0)),B1:B3)