Forum Discussion

Frank Spade's avatar
Frank Spade
Copper Contributor
Apr 09, 2022
Solved

How to determine if there is a non-empty cell in a range

I have tried for over an hour to find a solution to this simple question and finally give up.   ISBLANK(A1:AD3) gave an overflow warning.   Can somebody please tell me the answer?
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 09, 2022

    Frank Spade 

    As variant

    =IF( SUM(--NOT(ISBLANK(A1:AD3) ) ), "have some", "all are blank" )

     

Frank Spade's avatar
Frank Spade
Copper Contributor
Apr 09, 2022

Thank you.

What are the spaces for? It works well without.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 09, 2022

Frank Spade , you are welcome.

Spaces are for better readability, that's up to you use them or not. Doesn't affect calculations.

  • Frank Spade's avatar
    Frank Spade
    Copper Contributor
    Apr 09, 2022
    Sorry, it looks weird to me. Who said, its better readable? I didn't learn it at the university.
    Thanks anyway.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 09, 2022

      Frank Spade 

      Again, that's your choice how to write formulas,

      such way

      =LAMBDA(Array1,Array2,Defaults,LET(R,ROWS(Array1)+ROWS(Array2),C,MAX(COLUMNS(Array1),COLUMNS(Array2)),D,IF(COLUMNS(Defaults)>1,INDEX(Defaults,1,0),LEFT(INDEX(Defaults,1,1),SEQUENCE(1,C,LEN(INDEX(Defaults,1,1)),0))),X,SEQUENCE(1,C),Y,SEQUENCE(R),IF(Y<ROWS(Array1)+1,IFERROR(INDEX(Array1,Y,X),IFERROR(INDEX(D,1,X),"")),IFERROR(INDEX(Array2,Y-ROWS(Array1),X),IFERROR(INDEX(D,1,X),"")))))

      or like

      =LAMBDA(arrayA, arrayB,
    HSTACK(
        TOCOL( IF(SEQUENCE(, ROWS(arrayB)), arrayA)),
        TOCOL( IF(SEQUENCE(, ROWS(arrayA)), arrayB), , 1)
    )
)

      If you consider that first way is much better - that's okay. As far as that's okay for people with whom you share your formulas.

Resources