Excel 2007 : Help needed with logical function

**zoyter2** · 06-19-2011, 02:37 AM

All, I hope someone here can help me out. I am trying to make an Excel roster of membership for an organization. Each row will contain data relevant to the member, with Column A listing a status. Columns P, Q and R will contain dates. If there is a date in P with Q and R empty, I want column A to reflect "EA". If there is a date in P AND Q, Column A will show "FC". It P, Q and R all have a date, Column A should read "MM".

Here is what I have managed to make. Note that when used, only the last scenario works properly. That section I have posted in red. IE- if all three have a date, "MM" appears on the Column A.

=IF(AND(P2>0,Q2<0,R2<0),"EA",IF(AND(P2>0,Q2>0,R2<0),"FC",IF(AND(P2>0,Q2>0,R2>0),"MM")))

I am about to throw my hands up! Thanks for taking the time to read this, maybe someone can help out! Ross

**ConneXionLost** · 06-19-2011, 02:48 AM

Hi,

How about this:

Please Login or Register  to view this content.

Cheers,

**JBeaucaire** · 06-19-2011, 02:49 AM

It might be as simple as:

=CHOOSE(COUNT($P2:$R2), "EA", "FC", "MM")

...depends on how orderly those dates are entered.

**JBeaucaire** · 06-19-2011, 02:51 AM

A better one might be:

=LOOKUP(2, 1/($P2:$R2>0), {"EA","FC","MM"})

This one would still give you "FC" if Q had a date and P was accidentally empty.

**zoyter2** · 06-21-2011, 02:24 PM

Originally Posted by JBeaucaire

It might be as simple as:

=CHOOSE(COUNT($P2:$R2), "EA", "FC", "MM")

...depends on how orderly those dates are entered.

You are right JB...it WAS this simple!

You guys are all awesome. Thanks for the help. Ross

**royUK** · 06-21-2011, 02:46 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save