# Faq/Calc/141

TDF LibreOffice Document Liberation Project Community Blogs Pootle Nextcloud Redmine Ask LibreOffice Donate

## 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*^{[note 1]}) 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:

- ↑ A percentile is each of the 99 values which divide the sorted data in 100 equal parts, so that each part represents 1⁄100 of the sample population. A percentile returns the value for a data series running from the smallest to the largest value in a set of data. For Alpha = 25, the percentile means the first quartile = 50 Alpha is the MEDIAN.