PDA

View Full Version : IF Statement in EXCEL



Iamaterp
03-23-2005, 07:25 AM
I am having trouble with the following IF Statement



I have 3 columns, Member Id & Begin Date & End Date...each member has multiple rows...I first sorted the sheet by Member Id and than Begin Date to order the document

I would like to place the word "TRUE" on the following condition:

-If a member has an End Date and than has another Begin Date within 0 to 7 days

-I would like to put "TRUE" on both lines, with the Begin and End Date that qualified the member to be "TRUE"



Here is what I have thus far, however, I am not able to get "TRUE" on Both lines

=IF(((A2=A3)*AND(I3-J2<=7)),"TRUE","FALSE")



Example:

Member Id Begin Date End Date

1 1/1/05 2/1/05 TRUE

1 2/5/05 2/8/05 TRUE

1 3/1/05 3/3/05 FALSE

2 3/4/05 4/1/05 FALSE *member Id changed

TonyJollans
03-23-2005, 11:04 AM
Hi Iamaterp,

Try this:

=IF(OR(AND(A2=A3,(I3-J2)<=7),AND(A2=A1,(I2-J1)<=7)),TRUE)

.. in row 2 and copy down (you can't put it in row 1)

JonPeltier
03-23-2005, 11:25 AM
=IF(((A2=A3)*AND(I3-J2<=7)),"TRUE","FALSE")

I'll leave you to figure out the logic, but your syntax is not going to work. Here are a couple ways to proceed:

=IF(AND((A2=A3),(I3-J2<=7)),TRUE,FALSE)

=IF(A2=A3,if(I3-J2<=7,TRUE,FALSE),FALSE)

Notice also I've changed from text strings for "TRUE" and "FALSE" to the Boolean TRUE and FALSE.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______