| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AND & OR function
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND & OR function
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 "roger" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND & OR function
Try:
=if(or(a2="",a3=""),b1,"") "roger" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND & OR function
maybe
=IF(SUMPRODUCT(--ISBLANK(a2:a5))1,"there's a blank", "no blanks") "roger" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND & OR function
=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 "roger" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND & OR function
Hello,
Array-enter =OR(ISBLANK(A2:A5)) Regards, Bernd |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND & OR function
Same there, if the blanks are derived from formulas it will not work
-- Regards, Peo Sjoblom "Bernd P" wrote in message ... Hello, Array-enter =OR(ISBLANK(A2:A5)) Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
