CASE

Syntax

CASE (Condition A : Result A, Condition B : Result B {,...})

Description

The CASE function returns the Result that corresponds to the first true Condition, if none of the conditions is true, it returns zero.

Returns

The Result that corresponds to the first true Condition; if none of the conditions is true, it returns zero.

Example

Suppose a company awards its salespeople the following commissions:

• A 10 percent commission if their sales are at least 50,000 USD.

• An 8 percent commission if their sales are at least 30,000 USD.

• A 5 percent commission if their sales are at least 15,000 USD.

You can calculate the commission rate for a salesperson with the following formula:

CASE(SALES >= 50000 : 0.10, SALES >= 30000 : 0.08, 
SALES >= 15000 : 0.05)

If SALES is 45000, this formula returns 0.08. Notice that the CASE function returns the result for the first true condition, even if some of the remaining conditions are true.

The above formula returns zero if SALES is less than 15000. Suppose that the company awards a 3 percent commission on all sales under 15,000 USD. You can model this with the following formula:

CASE(SALES >= 50000 : 0.10, SALES >= 30000 : 0.08, 
SALES >= 15000 : 0.05, #DEFAULT : 0.03)

The last condition (#DEFAULT) is always equivalent to TRUE, so the CASE function returns 0.03 if SALES is less than 15000. If you want the CASE function to return a default value other than zero, use #DEFAULT as the last condition.