Only Using Full Cells in Calc

[Blub123]

Hi there,

I have the following problem:

I'm using the following function: =MMULT(MMULT(Q65:Z65;$AI$76:$AK$78);MTRANS(Q65:S65))

and trying to create a data sheet where I can paste data into without knowing in advance how many variables I will have. The problem is however, that the cells have to be selected accordingly otherwise they return an error. Is there a way to tell the function to leave out empty cells, if there are any and to ONLY include full cells in the calc, i.e. something like DO the calc with the numbers in the cells Q65:Z65 IF only Q65:S65 is filled and the rest is empty, use these numbers. As it is set up now, it returns an error if there are empty cells.

I'm including a link below to show the situation.

https://gyazo.com/30838d46332473aafdca1fa9f67005bf 

[#2]

@Blub123 The screenshot does not give much information as it does not show the data in the ranges to which the formula refers. Moreover, with MMULT the first array should have the same number of columns as the second arrays has rows. Array 1 in the "inner MMULT" has 10 columns (Q to Z), where Array 2 has 3 rows (76-78). That will always result in a VALUE error.

Then, in the "outer MMULT" Array 2 seems to have 4 rows (1 row, 4 columns transposed to 4 rows, 1 column). Not clear what you are trying to do here.

And yes, MMULT must have numbers in all cells of the array. Blanks and texts produces the VALUE error as well.