Matrix multiplication with empty cells

[Ingrid Voigt]

Hi,

I've got matrices in my worksheet I'd like to
multiply using MMULT. However, as long as some
of the cells referred to are empty, I can't
get a result. Excel apparently doesn't set the
contents of these cells to 0 as I'd like it to.

Is there a way or a setting around this? When
multiplying "manually" (A1*B1 + A2*...) it
works properly. Filling up the cells with zeros
is not a good idea. (The table is a chess tournament
table where an empty cell indicates "game not played",
but a zero cell "game lost").

I'm using the German language version of Excel 97
if that matters.


Regards
Ingrid

 

[Harlan Grove]

I've got matrices in my worksheet I'd like to
multiply using MMULT. However, as long as some
of the cells referred to are empty, I can't
get a result. Excel apparently doesn't set the
contents of these cells to 0 as I'd like it to.

....

Since you'd be entering these as array formulas anyway, if RngA and RngB
have blank cells, try

=MMULT(RngA+0,RngB+0)

 

[Vasant Nanavati]

Nice one, Harlan! Never thought of that.