Nested If Statement

[stak]

Hi I am hoping that someone can help me with this.

I have a spreadsheet that contains dates in column B and
column C.

Column B contains Closed Dates. Column C contains Dead
Dates.

I need to insert a formula so that if you look at columns
B & C - eg: Cell B2 and C2 and they contain dates, then I
want to display Closed, if a date appears in B3 only or
Closed/Dead if a date appears in B2 and C3. If a date
appears in C2 only, then I want to show Dead only.

Closed Dead Formula Answer
12/03/01 10/03/02 Dead Closed
09/01/00 06/10/02 Dead Closed
01/01/01 Dead
10/10/00 Dead
11/11/01 Closed
09/09/68 Dead
05/01/79 Dead
08/11/02 Closed


Any help would be most appreciated. I am desperate to
sort this one out.

Thanking you in advance.

Sue

 

[Don Guillett]

Have you tried

=IF(A2>0,"Dead","")&IF(B2>0," Closed","")

 

[J.E. McGimpsey]

one way:

=TRIM(IF(B2<>"","Closed ","") & IF(C2<>"","Dead",""))

 

[Guest]

-----Original Message-----
Hi I am hoping that someone can help me with this.

I have a spreadsheet that contains dates in column B and
column C.

Column B contains Closed Dates. Column C contains Dead
Dates.

I need to insert a formula so that if you look at columns
B & C - eg: Cell B2 and C2 and they contain dates, then I
want to display Closed, if a date appears in B3 only or
Closed/Dead if a date appears in B2 and C3. If a date
appears in C2 only, then I want to show Dead only.

Closed Dead Formula Answer
12/03/01 10/03/02 Dead Closed
09/01/00 06/10/02 Dead Closed
01/01/01 Dead
10/10/00 Dead
11/11/01 Closed
09/09/68 Dead
05/01/79 Dead
08/11/02 Closed


Any help would be most appreciated. I am desperate to
sort this one out.

Thanking you in advance.

Sue

.

Try this one in row two and then fill down

=if(and(c2<>" ",b2<>" "),"Dead Closed",if(and

(b2<>" ",c2=" "),"Dead",if(and
(c2<>" ",b2=" "),"Closed"," ")))

 

[Cade]

Hi I am hoping that someone can help me with this.

I have a spreadsheet that contains dates in column B and
column C.

Column B contains Closed Dates. Column C contains Dead
Dates.

I need to insert a formula so that if you look at columns
B & C - eg: Cell B2 and C2 and they contain dates, then I
want to display Closed, if a date appears in B3 only or
Closed/Dead if a date appears in B2 and C3. If a date
appears in C2 only, then I want to show Dead only.

Closed Dead Formula Answer
12/03/01 10/03/02 Dead Closed
09/01/00 06/10/02 Dead Closed
01/01/01 Dead
10/10/00 Dead
11/11/01 Closed
09/09/68 Dead
05/01/79 Dead
08/11/02 Closed


Any help would be most appreciated. I am desperate to
sort this one out.

Thanking you in advance.

Sue

Sue,

this will help you:

Assume that your first line of data is 10, paste the following formula
into C10:

=TRIM(IF(A10<>"","Dead","")&" "&IF(B10<>"","Closed",""))

You can then copy the formula down for as many rows that you have. If
your first data row is less than 10, simply replace the A10 and B10
with the row where you paste the formula into... quite simple =)

Cade