convert color to number
- Thread starter febausa
- Start date
Since the colors are determined by logical rules (in conditional formatting), it would be better to use those same logic rules, than to some how count the color.
I added a table in G1:K2 to show the different colors and their cut-offs. Then we can easily count the number of cells in each row that match criteria.
I added a table in G1:K2 to show the different colors and their cut-offs. Then we can easily count the number of cells in each row that match criteria.
eibi
Active Member
I suggest that rather than count colors, you count values -- using formulas that correspond to the ones used in your conditional formatting...
See attached. Is this the result you want?
Keep in mind that each column has a different formula.
Also -- a side note: The values 1 and 1.4 both go to orange in your table. Is this intentional? I assumed that 1.0 should be blue, but it tested yellow...
See attached. Is this the result you want?
Keep in mind that each column has a different formula.
Also -- a side note: The values 1 and 1.4 both go to orange in your table. Is this intentional? I assumed that 1.0 should be blue, but it tested yellow...
Hi eibi,
Not quite sure what you mean...if a cell in table has value of 1.0, it should be color yellow, and that's how my formulas count it.
Now, a better oddbal is 1.4, and that's because of how CF is defined (x = cell contents).
Blue is defined as 1 > x
Yellow is definedas 1 <= x <= 1.4
Orange is defined as 1.4 <= x <= 1.7
Green is 1.7 <= x <=2
Red is 2 < x
Between blue and yellow, we have a nice break, but technically both yellow and orange are true when x = 1.4. In this case, the order the rules are written in determines which color ends up applying to cell (note that this is bad practice, IMO).
The way I setup my table to count, I assumed breakdown should be:
Blue is defined as 1 > x
Yellow is definedas 1 <= x < 1.4
Orange is defined as 1.4 <= x < 1.7
Green is 1.7 <= x <2
Red is 2 <= x
Not quite sure what you mean...if a cell in table has value of 1.0, it should be color yellow, and that's how my formulas count it.
Now, a better oddbal is 1.4, and that's because of how CF is defined (x = cell contents).
Blue is defined as 1 > x
Yellow is definedas 1 <= x <= 1.4
Orange is defined as 1.4 <= x <= 1.7
Green is 1.7 <= x <=2
Red is 2 < x
Between blue and yellow, we have a nice break, but technically both yellow and orange are true when x = 1.4. In this case, the order the rules are written in determines which color ends up applying to cell (note that this is bad practice, IMO).
The way I setup my table to count, I assumed breakdown should be:
Blue is defined as 1 > x
Yellow is definedas 1 <= x < 1.4
Orange is defined as 1.4 <= x < 1.7
Green is 1.7 <= x <2
Red is 2 <= x
eibi
Active Member
Luke -- You're right -- 1.4 is the oddball; my mistake.
I'll have to look into the Custom Formatting a little more; I'm interested to learn how the order of rules affects their application -- I was working on another project and wondered about multiple layers of custom formatting. You'd say that's bad practice, though?
When you have two custom formatting ranges that intentionally overlap, would you write a new custom formatting formula for the overlapping section or would you simply allow the two rules to be applied over one another in that section?
I made up the attached example: overlapping cf
I'll have to look into the Custom Formatting a little more; I'm interested to learn how the order of rules affects their application -- I was working on another project and wondered about multiple layers of custom formatting. You'd say that's bad practice, though?
When you have two custom formatting ranges that intentionally overlap, would you write a new custom formatting formula for the overlapping section or would you simply allow the two rules to be applied over one another in that section?
I made up the attached example: overlapping cf
Last edited:
It's bad practice to have ambiguous rules, and the actual color ends up being decided by what order you decided to write the rules. Ideally, you'd define the ranges like I did in the 2nd part. You can't use one of the simple dropdowns, but you could write a formula like:
=AND(A2>=1,A2<1.4)
That way I know exactly what color is going to get applied, regardless of the order the the CF is in.
Side note, when you copy/paste cells that have CF, this can sometimes screw up the order of the rules, or create duplicate rules. This is why relying on rule order alone is not a good idea.
As for your example, I'd want to know more about why they overlap. See the attached for an example of why overlap doesn't really work well.
=AND(A2>=1,A2<1.4)
That way I know exactly what color is going to get applied, regardless of the order the the CF is in.
Side note, when you copy/paste cells that have CF, this can sometimes screw up the order of the rules, or create duplicate rules. This is why relying on rule order alone is not a good idea.
As for your example, I'd want to know more about why they overlap. See the attached for an example of why overlap doesn't really work well.