Enter a final status based on multiple cells values

Question

I have a worksheet that I need to determine a final result based on the status of four cells. The  four cells can be a number of variations. Please see example:

Columns ( different variations and the final result)

BO                         BP                              BQ                                BS                                        Final Status

Red                      Red                            Red                               Red                                        Order

Red                      Red                            Red                               Yellow                                    Order

Red                      Red                            Red                              Green                                      OK

Red                      Red                            Yellow                          Red                                         Order

Red                      Red                            Yellow                         Yellow                                       Watch

Red                      Red                            Yellow                         Green                                        OK

Red                      Yellow                       Yellow                          Red or Yellow                         Watch

Red                      Yellow                       Green                          Red or Yellow                          Watch

Yellow                  Red                           Red                              Red                                           Watch

Yellow                  Yellow                      Yellow/Red/Green    Yellow/Red/Green                   Watch

Is there a way to produce the Final Result with a macro?

Thank you.

Answer 1

OK, I've got it all now with that last post.  Thanks for your patience, hopefully this formula will make the wait worth it.

For row 2 (assuming Columns A,B,C and D for BO,BP,BQ and BS)

=IF(OR($A2="Green",AND(COUNTIF($A2:$B2,"Red")=2,$D2="Green")),"OK",IF(OR(COUNTIF($A2:$D2,"RED")=4,AND(COUNTIF($A2:$D2,"Red")=3,OR($C2="Yellow",$D2="Yellow"))),"Order","Watch"))

P.S. as it turns out, =IF(A2="Green","OK,"not ok") was almost it - for the one case where BO is Green, that does result in OK.  But of course other factors got involved later on.

The Rules for "OK"

If BO = Green then Final Status (FS) is OK

If BO and BP = Red AND BS = Green, then FS is OK

Rules for "Order"

If BO, BP, BQ and BS are all Red, FS is "Order"

If there are 3 Red entries in the row AND either BP or BQ is Yellow, FS is Order

Any other conditions are "Watch".

[Edit] Same disclaimer as Jeeped! :)

[2nd Edit] Fixed formula to actually work on row 2 vs 16, and made columns absolute so that it can be copied into other columns without failiing.

Answer 2

Can you provide a set of rules? I mocked up a bitwise COUNTIF() into a LOOKUP() function which seems to follow your sample for the most part, but unless the columns are given different credence, the pattern is lost to me. Rows 7 and 10 seem particularly out of place.

                     

The formula in F2 is,

=LOOKUP((COUNTIF($A2:$D2,"Red")*4)+(COUNTIF($A2:$D2,"Yellow")*2)+COUNTIF($A2:$D2,"Green"),{0,13,14},{"Watch","OK","Order"})

... which can be filled down as necessary.

Answer 3

This would not be the case, please refer to my initial post. there are approximately 11 patterns to consider. Final Status is not just based on column A's status. here are a few examples:

A                             B                                 C                                   D                                   Final Status

Red                      Red                            Yellow                         Green                                        OK

Red                      Yellow                       Green                          Red or Yellow                          Watch

Yellow                  Yellow                      Yellow/Red/Green    Yellow/Red/Green                   Watch

Answer 4

If there would always be an entry of some color in column A, then it becomes a very simple formula since it appears from this example of rules that the Final Status is [OK] only if column A has "Green" and in that case, the contents of the other 3 columns can be any entry.

=IF(A2="Green","OK","Watch")

Answer 5

The rules are the examples provided in each row, columns A - D in you snapshot?

For Rows 7 - 10, this is a pattern that could happen and the final status it should be.

A                            B                               C                                 D                                        Final Status

Red                      Yellow                       Yellow                          Red or Yellow                         Watch

Red                      Yellow                       Green                          Red or Yellow                          Watch

Yellow                  Red                           Red                              Red                                           Watch

Yellow                  Yellow                      Yellow/Red/Green    Yellow/Red/Green                   Watch

Green                    Red/Yellow            Yellow/Red/Green    Yellow/Red/Green                   OK

A little background, for each value in columns A - D,  the value is based off another cell value on the worksheet. The color status indicated is a status of the cell it refers too, For the statuses I provided in my original post these are the possible variations(patterns) and what the final status should be.

Note: 

• I added one additional pattern (in this reply), I missed in my original post.
• Some columns may only have two colors (i.e. example col B)

Hope this makes sense.