Joe: Can I set conditions such as if it’s between 1-3 red, 4-6 green and 7-10 yellow?
Brandy: Is it possible to have multiple conditions?
Yes, since Custom Formatting enables you to format 4 types of values (positive, negative, zero, and text), it is possible to have up to 4 conditions.
This means that so long as your conditions are setup to be one of these types, custom formatting can be applied.
In an example, the conditions are:
- 5-10%: light green ■
- 3-5%: yellow
- >10%: dark green
- <3%: red
Notice that under these conditions, it is quite possible to not have any negative values.
However, none of them should actually be a 0 or text, since formulas will be used to translate the values for each condition into one of the 4 types.
- 5-10%: light green ■ = Set as positive
- 3-5%: yellow = Set as negative
- >10%: dark green = Set as 0
- <3%: red = Set as text
This is done through the nested IF() formulas incorporating the following logical_text below for each condition.
Starting at cell O6:
- IF(AND(N6>=5%, N6<=10%), 1 = this assigns values between 5-10% to a positive number 1
- IF(AND(N6>=3%, N6<=5%), -1 = this assigns values between 3-5% to a negative number 1
- IF(N6 > 10%, 0 = this assigns values above 10% to a 0
- “t” = this assigns everything else to a text
The final formula becomes:
Cell O6 = IF(AND(N6>=5%, N6<=10%), 1, IF(AND(N6>=3%, N6<=5%), -1, IF(N6 > 10%, 0, “t”)))
Note that these values: 1, -1, 0, and t are placeholders that fall under each type.
This can be changed to any other value of the same type.
Drag the formula down to the last row of the table.
Copy the symbol to be used.
In this case, ■.
Highlight the area where custom formatting will be applied.
Press CTRL + 1 to bring up the custom formatting window.
Select the Custom category.
Define the custom formatting to be used under the Type section.
Use (this link) to get equivalent color codes from the MSDN website.
Type: [formatting of positive values]; [formatting of negative values]; [formatting of zeroes]; [formatting of text]
In this case, we will use: [color43] ■; [yellow] ■; [color10]■; [red] ■
Once applied, the values of the cells will change into the custom formatting.
It is also possible to set the thresholds as dynamic by doing cell references.
The 4 value types are at your disposal, so you can get creative in using them.