Please help me with simple IF worksheet function.

de8212 · May 1st, 2002, 05:43 AM

I have a worksheet function that looks like this IF(A1=1,"open","closed")
THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this:
When A1 is 0, I want it to say "Closed"
When A1 is 1, I want it to say "Open"
When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed.
THanks for any help.
de

Aladin Akyurek · May 1st, 2002, 05:48 AM

Quote:

On 2002-05-01 04:43, de8212 wrote:
I have a worksheet function that looks like this IF(A1=1,"open","closed")
THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this:
When A1 is 0, I want it to say "Closed"
When A1 is 1, I want it to say "Open"
When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed.
THanks for any help.
de

=IF(LEN(A1),IF(A1="???","???",IF(A1=1,"Open","Closed")),"")

Wayne Duncan · May 1st, 2002, 05:57 AM

Try: =if(a1=1,"open",if(a1=0,"closed",""))
I presume by ??? you mean anything other than 1 or 0.
Aladin: Why is your formula so complicated, does it have some advantages I can't see?

Quote:

On 2002-05-01 04:43, de8212 wrote:
I have a worksheet function that looks like this IF(A1=1,"open","closed")
THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this:
When A1 is 0, I want it to say "Closed"
When A1 is 1, I want it to say "Open"
When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed.
THanks for any help.
de

Aladin Akyurek · May 1st, 2002, 06:11 AM

Quote:

On 2002-05-01 04:57, Wayne Duncan wrote:
Try: =if(a1=1,"open",if(a1=0,"closed",""))
I presume by ??? you mean anything other than 1 or 0.
Aladin: Why is your formula so complicated, does it have some advantages I can't see?

Wayne,

I know it looks a bit ludic. Amendable though if it covers too much.

You had to make a caveat: I presume by ??? you mean anything other than 1 or 0.

I took "???" as a possible value of A1.

Try your version when A1 is truly blank. Hard to tell whether its result is intended.

Aladin

de8212 · May 1st, 2002, 07:11 AM

Thanks for the responses. I will try and see if I can get it to work.

Aladin
The data is automatically generated into the spreadsheet and it will only be a zero, a one, or ???. So ??? is definately the actual entry into the spreadsheet.
Thanks alot.

de

de8212 · May 4th, 2002, 09:06 AM

Worked great. Thank you for the help.

de

sen_edp · May 4th, 2002, 09:13 AM

When Aladin writes a formula ,
he looks very far ,and he is absolutely correct !!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 1st, 2002, 05:43 AM	#1
de8212 Board Regular Join Date: Apr 2002 Posts: 62	I have a worksheet function that looks like this IF(A1=1,"open","closed") THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this: When A1 is 0, I want it to say "Closed" When A1 is 1, I want it to say "Open" When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed. THanks for any help. de

May 1st, 2002, 07:11 AM	#5
de8212 Board Regular Join Date: Apr 2002 Posts: 62	Thanks for the responses. I will try and see if I can get it to work. Aladin The data is automatically generated into the spreadsheet and it will only be a zero, a one, or ???. So ??? is definately the actual entry into the spreadsheet. Thanks alot. de

May 4th, 2002, 09:06 AM	#6
de8212 Board Regular Join Date: Apr 2002 Posts: 62	Worked great. Thank you for the help. de

May 4th, 2002, 09:13 AM	#7
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	When Aladin writes a formula , he looks very far ,and he is absolutely correct !!!