1

I am new to array formulae and am having trouble with the following scenario:

I have the following matrix:

F   G   H   I   J  ...  R   S   T   U   V
1   0   0   1   1                       
    0   1       1       1   2   3   1   2
2   0   2   3   1       2   0   1   0   0
2   1   0   0   1       0   0   3   0   0

My goal is to count the number of rows within which the difference between the sum of columns F:J and the sum of columns R:V is greater than a threshold. Critically, only rows with full data should be included: row 1 (where there are only values for columns F1:J1) and row 2 (where there are only some values for columns F2:J2) should be ignored.

If the threshold = 2.5, then the solution is 1. That is, row 3 is the only row with complete data where the difference between the sum of F3:J3 (8) and the sum of R3:V3 (3) is greater than 2.5 (e.g., 5 > 2.5).

I have tried to put together the following formula, rather pathetically, based on the teachings of @Tom Sharpe and @QHarr:

=COUNT(IF(SUBTOTAL(9,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(9,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>2.5,IF(AND(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(9,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))),IF(AND(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(9,OFFSET(R1,ROW(R1:V1)-ROW(R1),0,1,COLUMNS(R1:V1))))))

But it seems to always produce a value of 1, even if I edit the matrix such that the difference between the sum of F4:J4 and R4:v4 also exceeds 2.5. Sadly I am struggling to understand why and would appreciate any guidance on the matter.

PyjamaNinja
  • 293
  • 2
  • 8
  • 1
    The short answer is probably that AND doesn't generally work in array formulas, but will have a look at it. – Tom Sharpe Jul 26 '18 at 12:19

5 Answers5

2

Try this:

=SUMPRODUCT((MMULT(F1:J4-R1:V4,--(ROW(INDIRECT("1:"&COLUMNS(F1:J4)))>0))>2.5)*(MMULT((LEN(F1:J4)>0)+(LEN(R1:V4)>0),--(ROW(INDIRECT("1:"&COLUMNS(F1:J4)))>0))=(COLUMNS(F1:J4)+COLUMNS(R1:V4))))

enter image description here

jblood94
  • 10,340
  • 1
  • 10
  • 15
2

Maybe, in say X1 (assuming you have labelled your columns):

=COUNTIF(Y:Y,TRUE)

In Y1 whatever your chosen cutoff (eg 2.5) and in Y2:

=((COUNTBLANK(F2:J2)+COUNTBLANK(R2:V2)=0)*SUM(F2:J2)-SUM(R2:V2))>Y$1

copied down to suit.

pnuts
  • 58,317
  • 11
  • 87
  • 139
2

I think this will do it, replacing your AND's by multiplies (*):

=SUMPRODUCT(--((SUBTOTAL(9,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(9,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>2.5)*(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))

It could be simplified a bit more but a bit short of time.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
2

As an array formula in one cell without volatile functions:

=SUM((MMULT(--(LEN(F2:J5)*LEN(R2:V5)>0),--TRANSPOSE(COLUMN(F2:J2)>0))=5)*(MMULT(F2:J5-R2:V5,TRANSPOSE(--(COLUMN(F2:J2)>0)))>2.5))

should do the trick :D

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • 1
    This is actually the best answer - I didn't go down the mmult road because it was OP's first experience of array formulas and I thought the offset way was a little easier to understand. Like @pnuts, can be tripped up in testing it because F2 looked like it was blank but contained some text. – Tom Sharpe Jul 26 '18 at 16:34
  • 1
    @TomSharpe I was struggling to find a 'best answer'. pnut's answer was briefest, jblood94's answer was cleanest, and your answer was most understandable (to me). You also gave a reason why my original code may not have worked. But given your comment I feel it is only right for future viewers to award it to Dirk Reichel. But it looks like we all learned something. Thank you all – PyjamaNinja Jul 26 '18 at 19:03
  • 1
    In one of my several edits, I had something very similar, but decided to go with `ROW(INDIRECT("1:"&COLUMNS(F1:J4)))` to avoid the array formula (I've never been able to get `SUMPRODUCT` to correctly calculate `TRANSPOSE` without entering it as an array formula). – jblood94 Jul 26 '18 at 21:32
  • 1
    @jblood94 checking your edits, you are right: it looks VERY similar. I’m confused. AFAIK giving different answers to one question or having multiple solutions in one answer is absolutely valid. – Dirk Reichel Jul 29 '18 at 09:11
  • 1
    That would have been best. It all worked out in the end. The OP got multiple good answers. – jblood94 Jul 29 '18 at 14:46
1

Just another option...

=IF(NOT(OR(IFERROR(MATCH(TRUE,ISBLANK(F1:J1),0),FALSE),IFERROR(MATCH(TRUE,ISBLANK(R1:V1),0),FALSE))), SUBTOTAL(9,F1:J1)-SUBTOTAL(9,R1:V1), "Missing Value(s)")

![![![My results

My approach was a little different from what you tried to adapt from @TomSharp in that I'm validating the cells have data (not blank) and then perform the calculation, othewise return an error message. This is still an array function call, so when you enter the formulas, press ctrl+shft+enter.

The condition part of the opening if() checks to see that each range's cells are not blank: if a match( true= isblank(cell)) means a cell is blank (bad), if no match ... ie no blank cells, Match will return an #NA "error" (good). False is good = Errors found ? No. ((ie no blank cells))

Then the threshold condition becomes:

=COUNTIF(X1:X4,">"&Threshold)' Note: no Array formula here

I gave the threshold (Cell W6) a named range for read ablity.

Jbowman
  • 450
  • 3
  • 9