Set criteria to allow whole percentage only without decimals.
In the example shown, the data validation applied to B5:B9 is:
How this formula works
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 to allow we use TRUNC 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.