Explanation
Data validation rules are triggered when a user adds or changes a cell value. In this example, we are using a formula that checks that the input doesn’t already exist in the named range “emails”:
COUNTIF(ids,B5)<2
COUNTIF returns a count of the value in C5 inside the named range emails (C5:C9). If the count is less than 2, the expression returns TRUE and validation succeeds. If not, the expression returns FALSE and validation fails.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5.
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
Explanation
The Excel TRUNC function does no rounding, it just returns a truncated number. It has an optional second argument (num_digits) to specify precision. When num_digits is not provided, it defaults to zero. In this formula for data validation, we use TRUNC to get the non-decimal part of a percentage, after we multiply the percentage by 100.
For example, if a user inputs 15%:
=TRUNC(.15*100)=(.15*100)
=TRUNC(15)=(15)
=15=15
=TRUE
If a user enters 15.5%, the formula evaluates like this
=TRUNC(.155*100)=(.155*100)
=TRUNC(15.5)=(15.5)
=15=15.5
=FALSE
This formula doesn’t validate anything else, for example, that percentages are less than 100%. Additional conditions can be added with the AND function.
Notes: Data validation rules are triggered when a user adds or changes a cell value. Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case, B5.
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
