2

I want to calculate matrix determinants (using the function "MDETERM"), the size of which are contingent on the value AL33 in the example below. I can use either the Excel 2010 row number/column number method for "address" or the "cell(address..." technique (as I've done below) to get the references I would like to put into the array for the MDETERM function, but apparently the references are only seen as text, and when I try as a formula:

=MDETERM(CELL("address",INDEX(AJ39:AS39,11-MATCH(AL33,AS39:AS48))):CELL("address",INDEX(AS39:AS48,MATCH(AL33,AS39:AS48))),

I get the message "the formula you typed has an error"

I use 2010, and have tried different methods with the EVAL (both with and without VBA) function in an attempt to make the address result "readable", but to no avail.

0

The sentence, "Use INDIRECT when you need to convert a reference assembled as text into a proper reference," from ExcelJet turned the light on for me about the INDIRECT function - it makes the references obtained from the CELL("address", [reference]) command usable in this amended formula:

=MDETERM(INDIRECT(CELL("address",INDEX(AJ39:AS39,11-MATCH(AL33,AS39:AS48)))):INDIRECT(CELL("address",INDEX(AS39:AS48,MATCH(AL33,AS39:AS48)))))

| improve this answer | |
  • Hi. Be aware of the volatile nature of the INDIRECT function. Try to have as few of those as possible, or your spreadsheet will become very performance hungry. – kolcinx Oct 5 '17 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.