Array Formula which ignores blank cells / only calculates cells with values

persisting the A:Z approach then, perhaps, something like the below will address your null string issue:

{=mmult(transpose(--(0&$A$2:$Z$100)),--(0&$A$2:$Z$100)))/(count($A$2:$A$100)-1)}

alternatively, if your 'active' matrix (i.e. A:D rather than A:Z) would never store interspersed null strings then you might consider switching to a dynamic named range approach, e.g.:

Name: =_Data
RefersTo: =$A$2:INDEX($A:$Z,MATCH(9.99E+307,$A:$A),MATCH(9.99E+307,$2:$2))

at which point your formula would become:

{=mmult(transpose(_Data),_Data))/(count(index(_Data,0,1))-1)}

So, firstly, if you go down this route I would certainly advocate creating a Named Range (_Data) to house the dynamic calc, rather than repeat the dynamic calcs in each part of your MMULT.

w.r.t 9.99E+307 -- this is simply a very big (exponential) number, i.e. 9.99*10^307

when used in "binary search" context - e.g

=MATCH(9.99E+307,$A:$A)
or
=LOOKUP(9.99E+307,$A:$A)

this can be used to isolate the last numeric value in a range - the first above would return the row in which it is found, the second would return the value itself

if you're interested in how this works check out Jon von der Heyden's write up: http://excelevolution.com/lookups-un...rch-algorithm/
(specifically: Dynamic Ranges with Binary Search)

in terms of why you calc isn't working.... obviously doing this without data but, I can see a couple of issues, one definite - one potential:

Issue 1 -- mismatched ranges:

INDEX($B:$ALM,MATCH(9.99E+307,$B:$B),MATCH(9.99E+307,$1132:$1132))

in the above your INDEX range should be $A:$ALM rather than $B:$ALM, this is because your "column" MATCH is referencing $1132:$1132 rather than $B$1132:$ALM$1132
so, if the last number were in C, the MATCH would return 3, and the 3rd column of your current INDEX range is D, rather than C.... so you get a disjoin.

that being said, given all elements have the same error, whilst this would certainly not generate the correct results I am not sure why you would get #REF!
(does the column to the right of the last numeric column hold #REF! errors?)

issue 2: change in approach?

the construct of the latest formula does not mirror that of your initial post -- your initial post:

=MMULT(Transpose(range),range)/(COUNT(range[column1])-1)

whereas your latest has the divisor being applied against the 2nd range within the MMULT

=MMULT(Transpose(range),range/(COUNT(range[column1])-1))

Your latest formula, using the initial construct (and with issue 1 resolved), should read:

{=MMULT(TRANSPOSE($B$1132:INDEX($A:$ALM,MATCH(9.99E+307,$B:$B),MATCH(9.99E+307,$1132:$1132))),$B$1132:INDEX($A:$ALM,MATCH(9.99E+307,$B:$B),MATCH(9.99E+307,$1132:$1132)))/(COUNT($B$1132:INDEX($B:$B,MATCH(9.99E+307,$B:$B)))-1)}

but, just to reiterate, I would create the dynamic named range, and then use the approach outlined in post 2.

Firstly XLent, I just want to say thank you for the great attention and knowledge you have shared with me so far, it is genuinely appreciated!

I have been playing about with the formula for some time now. I have attempted the first recommendation of a named dynamic range, it did not provide a result. Secondly, I pasted the formula at the end of your answer but it returned the following,

"Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command." It is also returning 0% values in every cell also.

I have made a copy of the workbook, exactly replicated with unnecessary information redacted. The cell references are exactly replicated and with the formula in place; hopefully to diagnose where I have went wrong. Hope this adds some clarity.

Again, your help is much appreciated!

[link removed]

Much appreciated XLent however when I attempted to upload the file it was over the size limit for uploads. I compressed the file, still too big. Used zip folder, too large; the Dropbox link was the only method left that I was aware of. Do you know of any workarounds I could use to upload the file?

Hi Xlent,

I can confirm everything is working great! I was testing it on the required sheets, working perfectly! Thank you so much, you really helped me out! All the best!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.