Forum Discussion

tomsuters's avatar
tomsuters
Copper Contributor
Jan 23, 2023
Solved

array formula gives wrong result when using generated cel ranges

has anyone encountered the problem shown in the figure below before? Is there a way to use generated cell ranges in combination with array functions?   Thx in advance  Tom Suters   PS I us...
excel
Formulas and Functions
  • tomsuters's avatar
    tomsuters
    Jan 25, 2023

    mtarler 

    Thx for pointing to the INDEX function. I actually found that a combination of the INDEX function with the INDIRECT works correctly as follows:

     

    =SUM(N(ISBLANK(INDEX(INDIRECT(ADRES(ROW();2;1;1) & ":" & ADRES(ROW();5;1;1));0))))

    In fact in this way I can use any function in combination with ADRES and INDIRECT to compute the cell range for counting blank cells. I need this in my application as the origin,, #rows and #columns of the cellrange are not fixed but are values computed elsewhere in the spreadsheet.

     

    With his workaround we can close the discussion I think. Thx to  mtarler and HansVogelaar  for participating!

     

    kind regards

    @tomsuters 

JosWoolley's avatar
JosWoolley
Iron Contributor
Feb 03, 2023

tomsuters 

 

I would suggest that Microsoft have revised the internal workings of the ROW function such that, when passed an array comprising a single value only, that array is converted to a non-array.

 

In legacy versions of Excel, the behaviour you are witnessing is perfectly normal: properly coerced, i.e. within some array-processing function, ROW always returned an array by default, even if that array was one comprising a single value, for example {1}, as opposed to 1.

 

This is still the case with O365 when the ROW function is passed an array of more than one value, as can be seen from, for example:

=SUM(N(ISBLANK(INDIRECT(ADDRESS(ROW(A1:A3),2,1,1)))))

for which the part:

ADDRESS(ROW(A1:A3),2,1,1)

resolves to:

{"$B$1";"$B$2";"$B$3"}

 

The following, however:

=SUM(N(ISBLANK(INDIRECT(ADDRESS(ROW(A1),2,1,1)))))

which in pre-O365 versions of Excel would evaluate to:

=SUM(N(ISBLANK(INDIRECT(ADDRESS({1},2,1,1)))))

i.e.:

=SUM(N(ISBLANK(INDIRECT({"$B$1"}))))

i.e.:

=SUM(N(ISBLANK(#VALUE!)))

is instead resolved in O365 to:

=SUM(N(ISBLANK(INDIRECT(ADDRESS(1,2,1,1)))))

i.e. the ROW function resolves to a non-array, which is then:

=SUM(N(ISBLANK(INDIRECT("$B$1"))))

i.e. 1.

 

Regards

Resources