Mmult with reference

[bines53]

Hello friends,

Is there a way to the < or > sign ,with reference.

=MMULT(--(B10:D15>C10:E15),{1;1;1}) It's OK.

In cell D1 appears > ,and I want to do MMULT(--(B10:D15"&D1&"C10:E15),{1;1;1}).

I checked several ways, without success.


Thank you !

David

 

[AliGW]

Untested, but try this:

=MMULT(--(INDIRECT("B10:D15"&D1&"C10:E15")),{1;1;1})

 

[bines53]

Hi AliGW ,

Does not work .

Thank you !

 

[bosco_yip]

Hi bines53,

Could you upload a file with example data and expected result for us to test.

Regards
Bosco

 

[bines53]

Hi Bosco,


Thank you !

 

[bosco_yip]

Hi bines53,,

Your expected result is a 1x6 array : ={2;2;3;3;3;3}



Maybe…..

1] Goto >> "Data" >> "Define name" >>

>> "Name" : Test

>> "Refer to" : =MMULT(--(EVALUATE("OFFSET($B$10,,,6,3)"&$D$1&"OFFSET($B$10,,1,6,3)")),{1;1;1})

2] In H14, enter :

=Test

3] See attached file

p.s. : EVALUATE is a Excel 4 Macro function, so the file need to save as xlsm

Regards
Bosco

 

[bines53]

Hi Bosco,

I thought there might be another way,

Thank you !

David

 

[Peter Bartholomew]

A less advanced approach would be to use the operator reference to choose the operation carried out within an expression, rather than use it directly
= N( IF( operator=">", previous>value, previous<value ) )
This could either be a named formula 'testArray' or could be built directly into the final worksheet formula
= MMULT( test_array, {1;1;1} )
= MMULT( N( IF( operator=">", previous>value, previous<value ) ), {1;1;1} )

 

[bines53]

Hi Peter,

Thank you !