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 us...
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)
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)
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?
- 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.- 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?