Faq/Calc/141

How to use Icon Set Conditional Formatting
We want to represent graphically the position of a value relative to certain thresholds

The menu allows you to set a condition of type  so that you can indicate the thresholds with suitable icons.

The available icon sets are:
 * 3, 4 or 5 arrows
 * 3, 4 or 5 gray arrows
 * 3 flags
 * 3 traffic lights 1 and 2 (two different designs)
 * 3 symbols (square, triangle, circle)
 * 3 smileys
 * 3 stars
 * 4 circles from red to black
 * 4 or 5 ratings
 * 5 quarters
 * 5 boxes

Conditions for displaying each icon can be specified relative to a value (Value), a percentage of the number of values ​​in the range (Percentage), as a percentage of range values ​​(Percentile ) or formula (Formula).

The following example illustrates how to implement 3 arrows:

Icons in column B represent the position of the note relative to the rounded average of the column, calculated in B12 with the formula:

=ROUND(AVERAGE($B$2:$B$10))

For thresholds referring to the result of a cell (and not directly to a value) we use the type Formula:

In column C we represent the evolution of the note in column B relative to the one in column A. Is the note greater than, equal to or less than A?

We introduce in column C a formula calculating the difference between notes. For example:

=B2-A2

The result will be negative (the second note is lesser than the first), equal (identical notes), or positive (the second note is greater than the first).

The thresholds may therefore here refer directly to a value and so we use this type:

Notes:

[[Media:FAQ.CALC 141 Iconeset.ods|Download an example spreadsheet]]