Excel conditional formatting icon sets formula
To have the conditions calculated automatically by Excel, you can express them using a formula.
To apply conditional formatting with formula-driven icons, start creating a custom icon set as described above. In the New Formatting Rule dialog box, from the Type dropdown box, select Formula, and insert your formula in the Value box.
For this example, the following formulas are used:
- Green flag is assigned to numbers greater than or equal to an average + 10:
=AVERAGE($B$2:$B$13)+10
- Yellow flag is assigned to numbers less than an average + 10 and greater than or equal to an average - 20.
=AVERAGE($B$2:$B$13)-20
- Green flag is used for values lower than an average - 20.

Note. It's not possible to use relative references in icon set formulas.