• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF, AND help

N

neillat

New Member
hello, I'm trying to figure out the right logic statement for a project mgmt file, that will give me a "Green" "Yellow or "Red" for a given set of conditions. I have totally confused myself, so I have stopped and seeked help :)


Here are the combo possibilities:


condition 1 condition 2 result

<0 +not started =red

<0 +open planning =red

<0 +open inflight =red

<0 +closed =green

>0,<5 +not started =yellow

>0,<5 +open planning =green

>0,<5 +open inflight =green

>0,<5 +closed =green

>5 +not started =green

>5 +open planning =green

>5 +open inflight =green

>5 +closed =green


Thank you in advance!


neillat.....
 
Hi Neillat ,


If you sort your data on the colours , the following is the result :


1. Yellow is only in one case - between 0 and 5 , and "Not Started"


2. Red is also in only one case ! - less than 0 , and Not "Closed"


3. Green is in all other cases.


Narayan


P.S. I am assuming that only the following 4 possibilities exist for your second condition - Closed , Open Planning , Open Inflight , Not Started.
 
I think I have this worked out. Your business rules are as follows:


1. All actions have a score from 0 to 5 (inclusive)

2. All actions exist in 1 of 4 conditions: Not Started, Open Planning, Open Inflight, Closed

3. An action can only have 1 score and exist in 1 condition

4. If an action has a score of 0 and is a condition other than "Closed", the status is Red

5. If an action has a score greater than 0 and is in a condition of "Not Started", the status is Yellow

6. All other actions have a status of Green


Assuming your spreadsheet looks like this:


A | B | C | D

1| Action | Score | Condition | Status

2| Action1 | 0 | Open Planning| Red


the formula in D2 would be


=IF(AND(A2=0,B2<>"Closed"),"Red",IF(AND(A2>0,B2="Not Started"),"Yellow","Green"))


The first IF-AND combination tests for a red status, which only happens if the Score is 0 and the condition something other than "Closed". If that statement is true, then D2 would be Red. If the statement is false, then the second IF-AND combination checks for Yellow, which looks for a score greater than 0 along with a condition of "Not Started". If that statement is true, the D2 reads yellow. If that statement is false, then D2 is green.


Hope this helps.
 
Thanks all for your input.


Sachin, the only thing I see missing is.... the "yellow" condition is triggered for from 0-5 and not started.

tx,Neillat
 
Sachin, this made it work: =IF(AND(G15<=0,H15<>"Closed"),"Red",IF(AND(G15<5,H15="Not Started"),"Yellow","Green"))
 
You must log in or register to reply here.
Back
Top