You can use formulas to highlight cells when the provided highlighting options do not meet your requirements.
To start off, you need to select the Use a formula to determine which cells to format option.
First, you need to reference the column(s) you want to use in the formula. You reference a column by enclosing the column name in square brackets e.g. [Sales], [Quantity], [Price] etc.
You then need to use a comparison operator, like =, <>, <, >, <=, or =>.
Finally, you need to provide a comparison value. This can be an absolute value, or another formula.
Some examples given the following table:
Description
|
Formula
|
Highlight sales exceeding 100
|
= [Sales] > 100
|
Highlight sales less than 50
|
= [Sales] < 50
|
Highlight sales value of 100 and above
|
= [Sales] >= 100
|
Highlight sales value of 50 and below
|
= [Sales] <= 100
|
Highlight sales where profit is 30% or more of sales
|
= [Profit]/[Sales] >= 0.3
|
Highlight sales where profit is 10% or less of sales
|
= [Profit]/[Sales] <= 0.1
|
Highlight above average sales
|
= [Sales] > AVERAGE([Sales])
|
Highlight sales exceeding 20% of the average sales
|
= [Sales] > (AVERAGE([Sales]) * 1.2)
|
Highlight sales above the median sales value
|
= [Sales] > MEDIAN([Sales])
|
Highlight sales above 100 and profit above 30
|
= AND ([Sales] > 100, [Profit] > 30)
|
Highlight sales above 100 or profit above 30
|
= OR ([Sales] > 100, [Profit] > 30)
|
Highlight sales above 100 in the state of California
|
= AND ([Sales] > 100, [State] = "California")
|
Arithmetic operators
You can use the following arithmetic operators in your formulas.
Operator
|
Description
|
+
|
addition
|
-
|
subtraction
|
*
|
multiplication
|
/
|
division
|
^
|
power
|
Statistical functions
You can use the following statistical functions in your formulas.
Operator
|
Description
|
AVERAGE
|
returns the average value
|
MIN
|
returns the minimum value
|
MAX
|
returns the maximum value
|
MEDIAN
|
returns the median value
|
SUM
|
returns the summation value
|
If you require any assistance writing your formulas, do drop us a line at support@yohz.com.
|