CODEWITHSUNDEEP
excel-formula
JSchwartz
JSchwartz

Reputation: 2724

EXCEL: How to use COLOR in a formula to determine a final result?

Let's say I have a row like this:

Grade  Q1      Q2    Q3
?      Green   Red   Green

The function for "Grade" (A2) needs to be as follows:

If 2 of 3 B2, B3, B4 (Q1, Q2, Q3) are color GREEN then Grade = PASS else Grade = FAIL

So, I need to check to see the color of Q1,Q2,Q3 and based on that put PASS or FAIL in GRADE cell. Any clues?

Thanks,

Upvotes: 0

Views: 7550

Answers (1)

teylyn
teylyn

Reputation: 35915

Try

=IF(COUNTIF(B2:B4,"green")>=2,"pass","fail")

This works when the cells have the text "Green" in them. It will not work if the cells have been colored with a fill color.

As a general rule: Color is not data. Many people run into problems when manually formatting cells with colors and then trying to aggregate this into information. Newer Excel versions have tools to filter and sort by format color in a table, but counting or summing by color is still not possible with Excel native formulas.

Instead of coloring cells, enter values. Enter the text values "Red" and "Green". Use conditional formatting to apply colors to cells based on their values. Then you can use formulas to aggregate (sum/count/average) by the cell values and still see the colors. The other benefit with conditional formatting is that the cell color changes immediately when the value changes from "Red" to "Green" for example.

Upvotes: 1

Related Questions