How to check if any single array or at least one of multiple arrays are empty? (non-VBA)

[Rnkhch]

Hello,

I define a lot of functions that take at least one array (range or defined name), and sometimes those arrays are empty which cause an error in the cell, which is not aesthetically pleasing To deal with this situation, I have so far wrapped my functions in IFERROR. However, I don't like this approach because if there happen to be other unrelated errors, I will never find out about them and so not fix them. So I'm trying to ditch IFERROR and find a more specific way of tackling this, and here's my two-part question.

A) I'm trying to come up with a generic way of checking if any single array (range or defined name) is empty. (I saw a post here, but that was VBA-based)

B) For those of my functions that take more than one array, is it possible to have a way of checking to see if at least one of them is empty? This will be so helpful instead if checking each of them one by one. Almost all of my functions are LET/LAMBDA-based nowadays, so I have something like this:

=LET(a,ARRAY1,b,ARRAY2,c,ARRAY3,operation)

I'm hoping to put the main 'operation' in an IF condition such that if at least one of the arrays are empty, then the function will return a blank (""), like this:

=LET(a,ARRAY1,b,ARRAY2,c,ARRAY3,IF('at least one array empty',"",operation))

Thanks for any input!

 

[Rick Rothstein]

There probably is a more direct way, but since you are using XL365, you can CONCAT your array and test its LEN...

=IF(LEN(CONCAT(ARRAY1))=0,"Array empty","Array not empty")

 

[Rnkhch]

Thanks, that's a great suggestion. I modified it like these to check more than one array, and it's working pretty well:

=IF(LEN(CONCAT(CONCAT(ARRAY1),CONCAT(ARRAY2),CONCAT(ARRAY3)))=0,"Array empty","Array not empty")

=IF(OR(LEN(CONCAT(ARRAY1))=0,LEN(CONCAT(ARRAY2))=0,LEN(CONCAT(ARRAY3))=0),"Array empty","Array not empty")

 

[Rick Rothstein]

CONCAT can concatenate non-contiguous ranges, so this should also work...

=IF(LEN(CONCAT(Array1,Array2,Array3)),"Array empty","Array not empty")

 

[Peter_SSs]

Not really a lot of difference but rather than
- create a result with CONCAT
- measure the length of that result
- compare that length to zero

you could just
- create a result with CONCAT
- compare that result to ""

=IF(CONCAT(ARRAY1)="","Array empty","Array not empty")