Conditional Maximum in Excel using Array Formulae - how to ignore Blank Rows

Question

There are quite a few questions on Stack Overflow about doing a conditional MIN and MAX in Excel e.g. Excel: Find min/max values in a column among those matched from another column

However, I don't think the following question is covered.

Normally the MIN and MAX functions will ignore blank rows, however it seems that if used in conjunction with a conditional array formula then they will NOT ignore.

For instance

If I enter the array formula =MAX(IF(A1:A8="A",B1:B8)) then I get zero, when I really want to see -1, since of all the non-blank 'A' rows, the maximum is -1.

I thought that the following array formula would work =MAX(IF(AND(A1:A8="A",B1:B8<>""),B1:B8)) but it ALWAYS returns zero

Answer 1

You nearly had it! However, in an array formula, you cannot replicate an "AND" construction so straightforwardly, in essence since the return from the AND function is always a single value, never an array.

Hence, your attempt:

=MAX(IF(AND(A1:A8="A",B1:B8<>""),B1:B8))

would initially correctly resolve to (using the values you posted):

=MAX(IF(AND({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}),B1:B8))

though the AND function would then look at that those two arrays of Boolean TRUE/FALSE returns and return a single value, i.e. FALSE (since there is at least one FALSE amongst those 16 entries).

The correct syntax would be:

=MAX(IF(A1:A8="A",IF(B1:B8<>"",B1:B8)))

Regards

Answer 2

you can test for blank in the if, and substitute an appropriate value if found.

The Array formula =MAX(IF(A1:A8="a",IF(ISBLANK(B1:B8),-999,B1:B8))) will substitute the value -999 for any blank cells, allowing you to find you maximum - alter the -999 depending on how low your numbers get