Data validation require specific multiple

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.