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 

tomsuters's avatar
tomsuters
Copper Contributor
Jan 23, 2023
correct. see my new post with the actual formula that caused the error. Seems very odd and would very much like to know if the error still exists in excel 365. Can you try it?
mtarler's avatar
mtarler
Silver Contributor
Jan 23, 2023
So that's interesting because for me #2 AND #3 result in 0
In both cases it is because SUM of T/F values is 0 and you have to convert them to be numerical so prefix the ISBLANK with -- or use N() and then both worked fine for me. But I'm also on Excel 365 with dynamic arrays so it could be completely different.
  • tomsuters's avatar
    tomsuters
    Copper Contributor
    Jan 23, 2023
    thx! So just to be sure: in 365 you use the exact formulas #2 and #3 (including the row() function) and you get the correct answer for both?
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jan 23, 2023
      I didn't use the exact formulas in #2 and#3 but did include the main parts including the INDIRECT and the ROW() and in BOTH cases got the "WRONG" answer initially because ISBLANK returns T/F not values. I then added the -- before the ISBLANK or used N( ISBLANK( ... ) ) then THEN in BOTH cases got the "correct" answer. AND these results were the same whether I used the dynamic array default in Excel 365 or used the CSE (ctrl-shift-enter) array format (but since it is still running in excel 365 I'm not sure my CSE equation will behave the same as CSE running in an older version of excel like yours
      • tomsuters's avatar
        tomsuters
        Copper Contributor
        Jan 24, 2023

        mtarler when stepping through the evaluation steps of my formula #3 (see pictures below), I discovered that Excel adds curly brackets around the result of the ROW() function.. This could be caused by the CSE action for the formula and 1) the fact that ROW() also allows array arguments and 2) is used inside the ISEMPTY() array function? I checked that the use of other non-array functions than ROW() inside ISEMPTY() do not create curly brackets around their result and do not cause errors.

         

        Maybe you can check ithrough formula evaluation that curly brackets do not appear around the ROW() result in your formula #3? That would at least show a difference between our CSE environments and would explain why the use of array capable functions within in array capable functions in my Excel version does not work.

         

        Thx for your time and effort.

         

         

Resources