How to use MMULT() when array contains blanks?

[Canelo69]

What I need is simple and described in the subject:

I would like to multiply matrices that contain blanks (treating blanks as 0). I read that using the formula N() would solve the problem, but it doesn't - probably N() does not work on matrices. 

Any help would be appreciated!

[#2]

@Canelo69 

You need to force auto-lifting with plus before the reference

[#3 · Accepted solution]

@Canelo69 

Or alternatively do some arithmetic which doesn't change the values, e.g.

=MMULT(1*array1,array2)

[#4]

Thanks @Sergei Baklan !

That is even better than what I just tried (which worked after trying: I tried a double TRANSPOSE() which did not work (don't understand why) and then I did a TRANSPOSE(1*TRANSPOSE(Array1)). But just doing the 1*Array1 is perfect!!

Made my day!

PS: What does "Auto-lifting" mean?

[#5]

@Canelo69 , you are welcome

Auto-lifting is the mechanism which allows functions, which were not designed to work with arrays natively, to do that. Actually they are applied to each element of the array and return array of the results.

For example, =LEN(array) returns the array with length of the each element. Or

=N( {1,0,3} ) returns an array {1,0,3}.

However, when we reference the range, not array, Excel first get the values from the range into array and after that apply the function to such array. That works with many functions, but not all. For example

=EDATE( TODAY(), range) returns and error and N( range ) returns the value for the first element of the range. To force transition from the range to the array we may add magic + before the reference on the range, =EDATE( TODAY(), +range) or N( +range ).

Such trick works with any data types. If in the range are only numbers or dates (actually also numbers) we may use as well =EDATE( TODAY(), 1*range) or =EDATE( TODAY(), range+0).

Since Excel doesn't work with blanks as values, with above operations all blank cells in the range are considered as zeroes.