Trying to get a formula to analyze 5 columns and update a 6th column based on the results of the 5

Jeff Hickey

BullandKhmer

Its just a big nestled if statement. i.e.

=IF(OR(col1@row="Red",col2@row="Red".......),"Red",IF(OR(countif(col1:col5@row,"Yellow')=1,countif(col1:col5@row,"Yellow')=2),"Yellow",IF(countif(col1:col5@row,"Yellow')>2,"Red",IF(OR(col1@row="",col2@row="".......),"Blue","Green"))))

Something like that. you can sort out the details.

I think there is a problem with your logic though as blue and other catregories are not mutually exclusive. So, when something qualifies as both blue and yellow or red, what colour do you want it to show? If you want it to be blue, you will want IF(OR(col1@row="",col2@row="".......),"Blue" at the start of the formula instead.

BullandKhmer

Its just a big nestled if statement. i.e.

=IF(OR(col1@row="Red",col2@row="Red".......),"Red",IF(OR(countif(col1:col5@row,"Yellow')=1,countif(col1:col5@row,"Yellow')=2),"Yellow",IF(countif(col1:col5@row,"Yellow')>2,"Red",IF(OR(col1@row="",col2@row="".......),"Blue","Green"))))

Something like that. you can sort out the details.

I think there is a problem with your logic though as blue and other catregories are not mutually exclusive. So, when something qualifies as both blue and yellow or red, what colour do you want it to show? If you want it to be blue, you will want IF(OR(col1@row="",col2@row="".......),"Blue" at the start of the formula instead.

Jeff Hickey

Awesome! Thank you so much for your help!

Jeff Hickey

Awesome! Thank you so much for your help!