Data validation whole percentage only

Data validation whole percentage only

Generic formula 

=MOD(A1*100,1)=0

Related formulas 

Data validation unique values only

Data validation allow numbers only

Data validation allow text only

Highlight integers only

Explanation

To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the MOD function. In the example shown, the data validation applied to B5:B9 is:

=MOD(C5*100,1)=0

How this formula works

The MOD function returns the remainder after pision. With a pisor of 1, MOD will return zero for any whole number.

We use this fact to construct a formula that tests the result of MOD. Because percentages are actually decimal values (i.e. 15% = .15) we need first shift the decimal in percentage values to the right so that we can test for whole numbers. We do this by multiplying the the user-entered amount by 100.

=MOD(C5*100,1)=0

This adjusted value goes into the MOD function with a pisor of 1. If the result is anything but zero the value is fractional at not allowed. The formula returns FALSE, and data validation fails.

Be aware this formula doesn't validate anything else, for example that percentages are less than 100%. Additional conditions could 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 whole percentage only

Generic formula 

=MOD(A1*100,1)=0

Related formulas 

Data validation unique values only

Data validation allow numbers only

Data validation allow text only

Highlight integers only

Explanation

To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the MOD function. In the example shown, the data validation applied to B5:B9 is:

=MOD(C5*100,1)=0

How this formula works

The MOD function returns the remainder after pision. With a pisor of 1, MOD will return zero for any whole number.

We use this fact to construct a formula that tests the result of MOD. Because percentages are actually decimal values (i.e. 15% = .15) we need first shift the decimal in percentage values to the right so that we can test for whole numbers. We do this by multiplying the the user-entered amount by 100.

=MOD(C5*100,1)=0

This adjusted value goes into the MOD function with a pisor of 1. If the result is anything but zero the value is fractional at not allowed. The formula returns FALSE, and data validation fails.

Be aware this formula doesn't validate anything else, for example that percentages are less than 100%. Additional conditions could 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.