I have a fairly simple issue that I cannot seem to work out. It may be familiar to some of you now.
I have the following matrix (which I will refer to as two arrays):
F G H I J ... R S T U V
1 0 0 1 1
4 4 2 3 5 1 2 3 1 2
2 2 3 1 2 0 1
2 1 0 0 4 0 0 3 0 0
I would like to take the difference between the average of each row in array 1 (columns F:J) and the average of each row in array 2 (columns R:V). For example, the average of F2:J2 = 3.6, the average of R2:V2 = 1.8, and the overall difference = 1.8. I would then like to count the number of overall differences which exceed a given threshold (e.g., 1), but I want to ignore rows which have no entries (see R1:V1) and/or partially missing entries (see the 2nd entry in row F3:J3 and 4th and 5th entry in row R3:V3).
I was lucky enough to be introduced to array formulae by @Tom Sharpe, and have attempted to adapt his code for a similar issue I had, e.g.,:
=SUMPRODUCT(--((SUBTOTAL(1,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(1,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>1)*(SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1))*(SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1))>0))
From what I understand, the code attempts to count the number of differences between the averages of each row in each array that exceed 1, so long as the product between the number of columns with full entries is >0 (i.e. has full data). However, it keeps throwing the #DIV/0! error, which I believe stems from that fact that it is still trying to subtract the average of F1:J1 and R1:V1 (e.g., the empty row), which would produce this kind of error. The correct answer for the current example is 1 (e.g., F2:J2 [3.6] - R2:V2 [1.8] = 1.8 == 1.8 > 1).
Does anyone have any ideas as to how the code can be attempted for the current purposes, and perhaps a v. brief explanation of what is going awry in the current code?
You're right, SUBTOTAL falls over when it's trying to find the average of an range containing only empty cells.
If you want to persevere and try and do it the same way as before with an array formula, you have to turn it round and put the condition for all the cells in both ranges to be non-blank in an if statement so that it doesn't try and take the average unless both ranges have no blanks:
=SUM(IF((SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1))*(SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1)),
--(SUBTOTAL(1,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(1,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>1)))
This time unfortunately I found I couldn't SUMPRODUCT it - I think this is because of the presence of the IF statement - so you have to enter it as an array formula using CtrlShiftEnter