Search code examples
excelarray-formulas

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

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

enter image description here

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

Solution

  • 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