Search code examples
excelarray-formulas

Excel, Array Formulas, N/A outside of range, and ROW()

I have a problem with ROW() in an array formula in Excel 2013.

Example:

I make a named range, called 'input', say 4 cells wide and 10 high. Then I make an array formula =ROW(input) one cell wide, 15 cells high.

I get 10 numbers - the first is the first row of input, and the rest count up from that, and then 5 #N/A follow. This is as it should be.

If instead of =ROW(input) I try one of the following:

=IFERROR(ROW(input),"x")

or

=IF(ISNA(ROW(input)),"x",ROW(input))

to catch the #N/As then what I expect to see is ten numbers, and then five x's. Instead I still see ten numbers and then five #N/As.

Can anyone tell me how to solve this problem? I want to get ten numbers, and then five x's.

And of lesser importance but just for curiosity (or in case it comes up in another case), why does this happen?

Why do I want to do this? It's part of a larger formula that I have simplified for this question.

Solution

  • I believe that, in such clauses, Excel gives precedence to the artificial expansion of the reference to match that of the worksheet range selected (which it will always do by filling with #N/As) over first resolving the IF clause over the array.

    So, whereas "normally" (e.g in a single-cell array formula), e.g.:

    =SUM(IF(ISNA(ROW(input)),"YES","NO"))

    would, effectively, coerce Excel into expanding the single value_if_true and value_if_false parameters of the IF function into syntactically-appropriate arrays of those values, viz:

    =SUM(IF({FALSE;FALSE;FALSE;FALSE;FALSE},{"YES","YES","YES","YES","YES"},{"NO","NO","NO","NO","NO"}))

    i.e.:

    =SUM({"NO";"NO";"NO";"NO";"NO"})

    with multi-cell array formulas, e.g. your:

    =IF(ISNA(ROW(input)),"YES","NO")

    entered over a 10-cell range, is NOT first resolved thus:

    =IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES"},{"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO"})

    (In which the the value_if_true and value_if_false parameters are first re-dimensioned in accordance with the dimension of the worksheet range in which the array is being entered.)

    i.e.:

    =IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE},{"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES"},{"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO"})

    i.e.:

    ={"NO";"NO";"NO";"NO";"NO";"YES";"YES";"YES";"YES";"YES"}

    but rather as:

    =IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"YES";"YES";"YES";"YES";"YES";#N/A;#N/A;#N/A;#N/A;#N/A},{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A})

    (The value_if_true and value_if_false parameters first being re-dimensioned in accordance rather with the dimensions of the Named Range input.)

    i.e.:

    =IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE},{"YES";"YES";"YES";"YES";"YES";#N/A;#N/A;#N/A;#N/A;#N/A},{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A})

    i.e.:

    {"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A}

    Hope that helps a bit.

    Regards