How to enable input of only certain multiples.(Microsoft Excel)
When entering numbers into a cell, you may want to limit the number to a specific multiple, such as a multiple of 100.
Here is how to set up such a case.
Steps
From "Data" select "Data Validation".
When the "Data Validation" window opens, change the "Input Value Type" to "Custom".
Then enter the formula as shown below. Use the MOD function for the formula.
=MOD(Cell,Multiples)=0
In this example, if a number other than a multiple of 50 is entered, an error message is displayed and the entry cannot be made.
However, Data Validation can be copied and pasted to ignore restrictions.
If you are wary of this, consider providing a check cell in a separate cell.
To do so, use the IF and MOD functions to set up formulas as follows.
=IF(MOD(Cell,Multiples)=0,"", "Error message")
---