array formula gives wrong result when using generated cel ranges

[tomsuters]

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

[#2]

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)

[#3]

@mtarler 

Your interpretation is correct.

[#4]

@tomsuters sorry for the dutch. I also showed he wrong formulas. See the formulas that actually caused the error. 

[#5]

indeed, you are correct, see my latest post below with the formula that actually caused the error.

[#6]

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?

[#7]

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.

[#8]

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?

[#9]

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

[#10]

@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.

[#11]

@tomsuters ok first my apologies.  now it doesn't work.  at least the cse version doesn't work.  the 'regular' dynamic array does work.  It is rather bizarre as the evaluate in both cases look identical until the last step:

w/o CSE:

and CSE:

AND ... both sequences are different than what you get,

BUT the CSE issue is still linked to the presence of ROW():

notice I used a fixed 8 instead of row() and trust me all the middle steps are identical and then:

very weird

[#12]

@mtarler thx for your reply. Indeed vey weird. Is there someone from Microsoft or another guru on this community that can 1) confirm our findings 2) explain why we get this error and 3) maybe even suggest a work-around?

[#13]

@tomsuters  @HansVogelaar  might be able to confirm and is as knowledgeable as they come as far as maybe giving insight on why and alternatives. 

I would recommend avoiding INDIRECT whenever possible and despite the appearance of ROW() being the problem I find things get funky when you use INDIRECT since you are converting things to text and then back to cell references. 

My suggestion is to try this:

{=SUM(N(ISBLANK(INDEX(F:H,ROW(),))))}

note the {} are not typed in but rather added by the CSE, but I think you know that. 

[#14]

@mtarler 

I have noticed weird behavior of ROW() and COLUMN() in array formulas.

I agree that using INDEX is more efficient and reliable than using INDIRECT

[#15 · Accepted solution]

@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 

[#16]

You are welcome and glad we were helpful.
To 'close' the discussion, please select a response as 'best response' to show a solution is found.
I will mention again that I would try to avoid INDIRECT if at all possible but if you are calculating row or column elsewhere and really don't have any other way I get it.
You may wonder why we try to avoid INDIRECT and other 'volatile' functions and that is if you use it for a whole column of data and the column gets big it forces excel to do a lot of extra calculations and may have significant impact on performance. Excel tries to be 'smart' and only calculate formulas/cells that might be impacted on any given update/change. So if you update A1 and B1 uses A1 in the formula then it re-calcs B1 but if C1 doesn't use A1 (nor B1 or any other cell that 'leads back' to A1) then it doesn't re-calc C1. Functions that are 'volatile' mean that excel can't be sure which cells may affect that calculation and therefore that cell (and therefore every other cell using that cell) will get re-calculated for each and every change. So even 1 little formula could cascade into a LOT of excess calculations on every change and result in performance issues. So just want you to know the 'cost' of using INDIRECT (and other 'volatile' functions)

[#17]

thx! I get it.

[#18]

@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

[#19]

@JosWoolley 

dear @JosWoolley @mtarler @HansVogelaar 

the background for this whole discussion was a Excel task for which I still fail to find a solution and that I now posted as a challenge to the whole: community: https://techcommunity.microsoft.com/t5/excel/challenge-find-column-formula-for-validation-of-rmandat...

 I look forward to your suggestions!

Tom