Problem with if statement

[Riaang]

Hi all,

I wonder if this is possible.

I have an excel spreadsheet with a Total column and leftdate or no date in the left column if the record is active.

What I need is a formula that will look at the total column and leftdate column simultanously. If the Amount in the total column is less than 0 and the leftdate is less than 31/12/2004 then I want in the next column LEFT CREDIT. If the amount in the total column is more than 0 and the leftdate is less than 31/12/2004 it must put in LEFT DEBIT. The same rule will apply for records with dates higher than 31/12/2004 or empty it must either give ACTIVE DEBITS or ACTIVE CREDITS.

EXAMPLE:

AMOUNT.....LEFT DATE.....TYPE
-2000.00.......................ACTIVE CREDIT
...300.00....01/01/2003...LEFT DEBIT
..-345.00....01/05/2005...ACTIVE CREDIT (LEFTDATE IN FUTURE)

I hope somebody can assist me.

Thanks

 

[PA HS Teacher]

Use the And function in and If.

e.g.

=If(And(A1=??,B1<??),"Left Debit","")

Replace ?? with your own criteria.

Edited to fix appearance of formula ~ Von Pookie

 

[Scott Huish]

How about:

Formula in C2:
=IF((B2="")+(B2>DATE(2004,12,31)),"ACTIVE ","LEFT ")& IF(A2<0,"CREDIT","DEBIT")

Copy down.

Book4
	A	B	C	D
1	AMOUNT	LEFTDATE	TYPE
2	-2,000.00		ACTIVECREDIT
3	300.00	1/1/2003	LEFTDEBIT
4	-345.00	1/5/2005	ACTIVECREDIT
Sheet1

 

[IML]

with amount in A1 and date in B1, try
=CHOOSE((B1<=--"12/31/04")+1,"Active","Left")&CHOOSE((A1>=0)+1," Debit"," Credit")

 

[Riaang]

Thanks a lot all you guys.

I have tested HOTPEPPER's formula and this is working 100%.

Thanks a lot Hotpepper.