Explanation

Data validation rules are triggered when a user adds or changes a cell value.

The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values.

Because we want to allow only dates in the month of June 2016, we give AND with two logicals.

The first logical tests that input to C5 is greater than or equal to June 1, 2016:

C5>=DATE(2016,6,1)

The second logical tests that input to C5 is less than or equal to June 30, 2016:

C5<=DATE(2016,6,30)

If both conditions are TRUE, the AND function returns TRUE and input passes validation. If either condition is FALSE, AND returns FALSE and input fails data validation.

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 C5.

Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists

Explanation

In this example, the goal is to create a data validation rule that will only accept numbers that are a specific multiple of another number. In the worksheet shown, the multiple is 100. Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When the formula returns FALSE, validation fails and the input is rejected. This means we need to create a formula that will return TRUE when input is a multiple of 100 and FALSE when input is not a multiple of 100. We can do this with the MOD function.

MOD function

The MOD function returns the remainder of two numbers after division. For example, with a number of 10 and a divisor of 3, MOD will return 1, the remainder after dividing 10 by 3:

=MOD(10,3) // returns 1

In the worksheet shown, we want to validate that entered in column C numbers are a multiple of 100. The formula used to validate input looks like this:

=MOD(C5,100)=0

The value in C5 is 500. The MOD function divides 500 by 100 and gets 5, with a remainder of zero. Since 0 = 0, the rule returns TRUE and the data validation passes:

=MOD(500,100)=0
=0=0
=TRUE

If a user enters, 550, the remainder is 50, and validation fails:

=MOD(C5,100)=0
=MOD(550,100)=0
=50=0
=FALSE

The formula returns FALSE and data 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, C5.

Fractional values

Although the example above deals with whole numbers, you can use the same approach to validate fractional values as well. For example, to require that the value entered in cell A1 be a multiple of 0.25, you can use a data validation rule with a formula like this:

=MOD(A1,0.25)=0

Effectively, this means the input to A1 must end in 0, 0.25, 0.50, or 0.75.

Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists