MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I only allow whole numbers in a cell???


Posted by Beast Boy on March 04, 2001 2:51 AM

I don't know how to only allow whole numbers in a column. I don't want to be able to enter any decimals or if I do then it would change it to a whole number. I tried the round function but the other cells didn recognise it. Is there a formula/command that can allow this to happen? Can anyone help me???


Posted by Aladin Akyurek on March 04, 2001 3:26 AM

One way to do it is to use Data Validation.

Select the column where you don't want to enter real numbers. Lets A be that column. Click on A, the column heading. Activate the option Data|Validation, choose Custom for Allow on the Settings tab, and type the following formula:

=A1-INT(A1)

Aladin

Posted by Beast Boy on March 04, 2001 3:51 AM

Now i can't get the cells to accept any numbers. It comes up with the error message "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Posted by Aladin Akyurek on March 04, 2001 4:09 AM

Whole numbers like 24, 10, etc should be accepted; the message you quote should appear only when you attempt to enter a real number like 10.1, 3.5, etc. If this is not the case, you must have made a mistake in the data validation procedure. Try again.

Posted by Aladin Akyurek on March 04, 2001 4:13 AM

Sorry, my mistake. The formula should be:

=A1-INT(A1)=0

Posted by Beast Boy on March 04, 2001 4:18 AM

Is it possible to do this over multiple cells? I think that is why I got the message before.

Posted by Aladin Akyurek on March 04, 2001 4:20 AM

No, multiple cells is not the reason. See my other post.

Posted by Beast Boy on March 04, 2001 4:31 AM

Hey Thanks
IT WORKED this time

Thanks again!!!
Beast Boy