Forum Discussion
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 use excel 2013 studentversion on Windows 10
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
- I only know english so i'm not sure if i'm interpreting the functions correctly but it appears you are trying to pass TEXT to a function and that is why you are getting 0
So ADDRESS & ":" & ADDRESS will produce a text string not a range reference. You would need to put that inside of an INDIRECT function (but would avoid that if possible)- indeed, you are correct, see my latest post below with the formula that actually caused the error.
Your interpretation is correct.
- 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?
