Operators
This section is a complete reference to all of the operators that you can use when you create expressions. This reference organizes the operators into the following categories:
*Operators in computed column expressions
*Operators in conditional formatting and filter condition expressions
Operators in computed column expressions
Table 12‑2 lists the operators you can use when you write expressions for a computed column.
Table 12‑2 Operators in computed column expressions
Operator
Use to
Example
+
Add two or more numeric values.
[OrderAmount] + [SalesTax]
‑
Subtract one numeric value from another.
[OrderAmount] ‑ [Discount]
*
Multiply numeric values.
[Price] * [Quantity]
/
Divide numeric values.
[Profit]/12
^
Raise a numeric value to a power.
[Length]^2
%
Specify a percent.
[Price] * 80%
=
Test if two values are equal.
IF([ProductName] = "1919 Ford Falcon", "Discontinued Item", [ProductName])
>
Test if one value is greater than another value.
IF([Total] > 5000, [Total]*15%, [Total]*10%)
<
Test if one value is less than another value.
IF([SalePrice] < [MSRP], "Below MSRP", "Above MSRP")
>=
Test if one value is greater than or equal to another value.
IF([Total] >= 5000, [Total]*15%, [Total]*10%)
<=
Test if one value is less than or equal to another value.
IF([SalePrice] <= [MSRP], "Below or equal to MSRP", "Above MSRP")
<>
Test if two values are not equal.
IF([Country] <> "USA", "Imported product", "Domestic product")
AND
Test if two or more conditions are true.
IF(([Gender] = "Male" AND [Salary] >= 150000 AND [Age] < 50), "Match found", "No match")
OR
Test if any one of multiple conditions is true.
IF(([City] = "Boston") OR ([City] = "San Francisco"), "U.S.", "Europe and Asia")
&
Concatenate string values.
[FirstName] & " " & [LastName]
Operators in conditional formatting and filter condition expressions
Table 12‑3 lists the operators you can use when you create expressions for conditional formatting and filter conditions.
Table 12‑3 Operators in conditional formatting and filter condition expressions
Operator
Use to
Example
Between
Test if a column value is between two specified values.
Profit Between 1000 and 2000
Bottom N
Test if a column value is among the lowest n values.
SalesAmount Bottom N 10
Bottom Percent
Test if a column value is in the bottom n percent of all values.
SalesAmount Bottom Percent 5
Equal to
Test if a column value is equal to a specified value.
Country Equal to France
Greater Than
Test if a column value is greater than a specified value.
Total Greater Than 5000
Greater Than or Equal to
Test if a column value is greater than or equal to a specified value.
Total Greater Than or Equal to 5000
In
Test if a column value is in the list of specified values. Use to select more than a single comparison value.
Country In USA, Canada, Mexico
Is Blank
Test if a column value is blank (" "). This operator applies only to string values.
E‑mail Is Blank
Is False
Test if a column value is false.
LoanApproved Is False
Is Not Blank
Test if a column value is not blank. This operator applies only to string values.
Email Is Not Blank
Is Not Null
Test if a column value is not a null value. A null value means that no value is supplied.
CreditLimit Is Not Null
Is Null
Tests if a column value is a null value. A null value means that no value is supplied.
CreditLimit Is Null
Is True
Test if a column value is true.
LoanApproved Is True
Less Than
Test if a column value is less than a specified value.
Total Less Than 5000
Less Than or Equal to
Test if a column value is less than or equal to a specified value.
Total Less Than or Equal to 5000
Like
Test if a column value matches a string pattern.
ProductName Like %Ford%
Match
Test if a column value matches a string pattern.
ProductCode Match S20
Not Between
Test if a column value is not between two specified values.
Profit Not Between 1000 and 2000
Not Equal to
Test if a column value is not equal to a specified value.
Country Not Equal to France
Not In
Test if a column value is not in the specified list of values.
Country Not In USA, Canada, Mexico
Not Like
Test if a column value does not match a string pattern.
ProductName Not Like %Ford%
Not Match
Test if a column value does not match a string pattern.
Product Code Not Match S10
Top N
Test if a column value is among the top n values.
SalesAmount Top N 10
Top Percent
Test if a column value is in the top n percent of all values.
SalesAmount Top Percent 5