Forum Discussion
How to use MMULT() when array contains blanks?
Or alternatively do some arithmetic which doesn't change the values, e.g.
=MMULT(1*array1,array2)
Or alternatively do some arithmetic which doesn't change the values, e.g.
=MMULT(1*array1,array2)Thanks SergeiBaklan !
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?
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.
