AND & OR function

[roger]

i want to check if any or all the cells a2:a5 are blank
i have tried IF((A2 or A3=""),B1,"") & IF((A2 AND A3=""),B1,"") this
returns NAME
and IF(A2:A5="",B2,"") this is the same as IF(A2="",B2,"") ie just checks
the first cell

the cells i am checking are either blank or x or X.

does anyone have a solution?

regards
Roger

 

[Baffled]

I have a solution, but its not very elegant
after your last column use
in A6 put =isblank(a2)
in A7 put =isblank(a3)
put this in for all the colum equivalents, "get the idea?
this will return true or false
then presuming you have 5 columns in the colum after your last isblank put
=if(a8=false,1,0)
then repeat that to cover all the columns youhave used
andthen at the ens do a simple sum calc and if that =0 you have no blanks,
and if its greater than 0 youdo
Bet that will not help much, but I know it works
D

 

[Iriemon]

Try:

=if(or(a2="",a3=""),b1,"")

 

[Duke Carey]

maybe

=IF(SUMPRODUCT(--ISBLANK(a2:a5))>1,"there's a blank", "no blanks")

 

[Peo Sjoblom]

=IF(SUMPRODUCT(A2:A5="")>0,B1,"")


=IF(OR(A2="",A3="",A4="",A5=""),B1,"")


=IF(COUNTBLANK(A2:A5),B1,"")


the last will not work if the values in A2:A5 are derived from formulas



--


Regards,


Peo Sjoblom

 

[Bernd P]

Hello,

Array-enter
=OR(ISBLANK(A2:A5))

Regards,
Bernd

 

[Peo Sjoblom]

Same there, if the blanks are derived from formulas it will not work


--


Regards,


Peo Sjoblom