Data Validation Conditional Operators

Conditional operators in the data validation rule builder can include these types of comparisons:

Numeric comparisons, using these operators: =, !=, <, <=, >, >=.
String value comparisons, using these operators: Equals, Not Equals, Contains, Starts With, Ends With, In, and Not In.

Rules can compare cells with different data types; for example, text and Smart List. The data type of the cell is honored if the referenced value always comes from one cell. This is the case when using Current Cell Value and Cross Dim Member to refer to a cell value. In cases where the value being compared comes from multiple cells (such as row value, column value, and cell value), the data type is defaulted to double.

When comparing values for these data types:

For double, a string representation is used for the double value, such as “123.45.” If the double is a whole number with no fractional part, such as 123.00, the integer value is used, for example, “123.”
For text, rules use only the text value for comparison.
All other data types (currency, non-currency, percentage, and date) are treated as double.

Table 27. Examples of Results for Starts With, Ends With, and Contains

Operator	Compare Value	Compare To Value
Starts With	2.0	2
	1234.0	12.0
	101.0	10
	2.0	2.0
	2.5	“2.”
	“YearTotal”	“Year”
Ends With	2.0	2.0
	2.0	2
	2.5	5
	2.5	“.5”
	“YearTotal”	“al”
	“YearTotal”	“Total”
Contains	2.0	2.0
	2.0	2
	2.5	5
	2.5	“.5”
	2.5	2.5
	23.567	3.5
	23.567	67
	23.567	“23.”
	23.567	“.56”
	“YearTotal”	“al”

For additional information about data validation, see: