Complicated nested IF AND OR statement….

Ok here goes….

So I am trying to evaluate two sets of values in two cells let’s just say, for the sake of argument, cell A1 and cell B1.

 

Cell A1’s values are:

0 – which means that the task in question is not started. (red)

1 – which means that the task in question is in progress. (yellow)

2 – which means that the task in question is in review. (yellow)

3 – which means that the task in question is completed and ready to be finalized. (green)

 

Got that? Ok. Now, cell B2’s values look like this:

1 = not on time. (red)

2 = going critical. (yellow)

3 = plenty of time yet. (green)

 

Now the formula must evaluate these two sets of values so that:

1.) If there is a green result in bothA1 and B1 the result is 3 (green).

2.) If there is a red result in either A1 or B1 the result is 1 (red).

3.) If there is a yellow result in both A1 or B1 the result is 2 (yellow)

4.) If there is a yellow result in either A1 or B1 and the other cell is 3 (green) the end result is 2 (yellow).

 

I know I need a nested IF((OR)) and perhaps an IF((AND)) statement, but I cannot get the bloody thing to evaluate re results corectly.

By: Mark

Leave a Reply

Your email address will not be published. Required fields are marked *