Formulas and Logical Functions: IF, AND, OR, IFS

The core summary functions

=SUM(Sales[Revenue])
=AVERAGE(Sales[Revenue])
=MIN(Sales[Units])
=MAX(Sales[Units])
=COUNT(Sales[Units])
=COUNTA(Sales[Rep])

COUNT counts cells that contain numbers. COUNTA counts cells that are not empty.

IF

IF tests a condition and returns one value when it is true and another when it is false.

=IF(logical_test, value_if_true, value_if_false)

Add a column called Size:

=IF([@Revenue]>=1000,"Large","Small")

Comparison operators

Operator	Meaning
=	equal to
<>	not equal to
> <	greater than, less than
>= <=	greater than or equal to, less than or equal to

Combining conditions with AND and OR

AND is true only if every condition is true. OR is true if at least one is.

=IF(AND([@Region]="North",[@Revenue]>=1000),"Priority","Standard")
=IF(OR([@Category]="Computers",[@Units]>=8),"Review","OK")

More than two outcomes

You can nest one IF inside another:

=IF([@Revenue]>=1500,"High",IF([@Revenue]>=500,"Medium","Low"))

IFS, available in Excel 2019 and later, is easier to read. It checks conditions in order and returns the result for the first one that is true:

=IFS([@Revenue]>=1500,"High",[@Revenue]>=500,"Medium",TRUE,"Low")

The final TRUE acts as “otherwise”.

Handling errors

A formula can return an error such as #DIV/0! or #N/A. IFERROR replaces any error with a value you choose:

=IFERROR([@Revenue]/[@Units],0)

Use it deliberately. Hiding every error can hide a real problem in the data.

Order of operations

Excel calculates brackets first, then powers, then multiplication and division, then addition and subtraction. =2+3*4 is 14, and =(2+3)*4 is 20. When in doubt, add brackets.

Practice

Add a Bonus column: 5% of Revenue when the rep is Asha and Revenue is at least 1000, otherwise 0.