Formulas

Top  Previous  Next

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.

 

formulas_using_02

 

 

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:

 

formulas_using_03

 

 

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.