I have a statement -
to check if both cells are blank then I need to populate with Not Assigned,
if anyone of the cells is not blank then I need it to populate with Assigned.

Here is the formula but it is not working
=IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
if one of the cells is not blank it popluates with NOT assigned but I want
it to show Assigned.

Thanks

--
mmk

Hi,

Change OR to AND

=IF(AND(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")

Mike

"MKuria" wrote:

> I have a statement -
> to check if both cells are blank then I need to populate with Not Assigned,
> if anyone of the cells is not blank then I need it to populate with Assigned.
>
> Here is the formula but it is not working
> =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
> if one of the cells is not blank it popluates with NOT assigned but I want
> it to show Assigned.
>
> Thanks
>
> --
> mmk

The formula =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
is working exactly how it should: if ANY ONE of the cell is blank then you
get "Not Assigned"

The formula =IF(AND(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
will give "Not assigned" only when BOTH cells are blank

So will =IF(COUNTA(AD2, AE2),"Assigned", "Not Assigned")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"MKuria" <(E-Mail Removed)> wrote in message
news:69645744-2A68-4A8F-A800-(E-Mail Removed)...
>I have a statement -
> to check if both cells are blank then I need to populate with Not
> Assigned,
> if anyone of the cells is not blank then I need it to populate with
> Assigned.
>
> Here is the formula but it is not working
> =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
> if one of the cells is not blank it popluates with NOT assigned but I want
> it to show Assigned.
>
> Thanks
>
> --
> mmk

Alternatively, you could use the following formula:

=IF(COUNTBLANK(your range)=2,"Not Assigned","Assigned")

Hope this helps?


"Mike H" <(E-Mail Removed)> wrote in message
news:506FCA46-D0F0-4EC7-A249-(E-Mail Removed)...
> Hi,
>
> Change OR to AND
>
> =IF(AND(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
>
> Mike
>
> "MKuria" wrote:
>
>> I have a statement -
>> to check if both cells are blank then I need to populate with Not
>> Assigned,
>> if anyone of the cells is not blank then I need it to populate with
>> Assigned.
>>
>> Here is the formula but it is not working
>> =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
>> if one of the cells is not blank it popluates with NOT assigned but I
>> want
>> it to show Assigned.
>>
>> Thanks
>>
>> --
>> mmk

=IF(OR(NOT(ISBLANK(A1)),NOT(ISBLANK(B1))),"Assigned","Unassigned")


"MKuria" wrote:

> I have a statement -
> to check if both cells are blank then I need to populate with Not Assigned,
> if anyone of the cells is not blank then I need it to populate with Assigned.
>
> Here is the formula but it is not working
> =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
> if one of the cells is not blank it popluates with NOT assigned but I want
> it to show Assigned.
>
> Thanks
>
> --
> mmk

Hi,

Either

=IF(AND(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")

or shorter:

=IF(AND(AD2="",AE2=""),"Not Assigned", "Assigned")

or shorter still

=IF(AND(AD2="",AE2=""),"Not ","")&"Assigned"

or even shorter:

=IF(COUNTA(AD2:AE2),,"Not ")&"Assigned"

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MKuria" wrote:

> I have a statement -
> to check if both cells are blank then I need to populate with Not Assigned,
> if anyone of the cells is not blank then I need it to populate with Assigned.
>
> Here is the formula but it is not working
> =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
> if one of the cells is not blank it popluates with NOT assigned but I want
> it to show Assigned.
>
> Thanks
>
> --
> mmk

A little shorter yet...

=IF(AD2&AE2="","Not ",)&"Assigned"

--
Rick (MVP - Excel)


"Shane Devenshire" <(E-Mail Removed)> wrote in message
news:E7102D9C-6336-4D33-8FA7-(E-Mail Removed)...
> Hi,
>
> Either
>
> =IF(AND(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
>
> or shorter:
>
> =IF(AND(AD2="",AE2=""),"Not Assigned", "Assigned")
>
> or shorter still
>
> =IF(AND(AD2="",AE2=""),"Not ","")&"Assigned"
>
> or even shorter:
>
> =IF(COUNTA(AD2:AE2),,"Not ")&"Assigned"
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "MKuria" wrote:
>
>> I have a statement -
>> to check if both cells are blank then I need to populate with Not
>> Assigned,
>> if anyone of the cells is not blank then I need it to populate with
>> Assigned.
>>
>> Here is the formula but it is not working
>> =IF(OR(ISBLANK(AD2),ISBLANK(AE2)),"Not Assigned", "Assigned")
>> if one of the cells is not blank it popluates with NOT assigned but I
>> want
>> it to show Assigned.
>>
>> Thanks
>>
>> --
>> mmk