Number validation

[Graham]

I am having a problem forcing someone to enter a number only between 0 and
100 in a cell. I thought it was relatively simple with data validation, ie
whole number between 0 and 100, or using a custom validation like
=ISNUMBER(C8). Thia works until someone enters a percentage eg 10%. The cell
accepts this as a number and at the same time converts the cell to a
percentage format. Is there any way I can allow the entry of a number only
between 0 and 100 and not accept the % at the end of the number. Grateful
for any guidance.

Kind regards
Graham Haughs
Turriff Scotland

 

[Frank Kabel]

Hi
one idea use the formula
=AND(ISNUMBER(C8),MOD(C8,1)=0,C8>=0;C8<=100)

 

[Graham]

Thanks Frank but it still takes in the percent and the cell behaves as a
percentage.

Graham

 

[Kent]

You said it right - Data Validation

Click in the cell and Choose Data->Validation
In the dialog box for "Allow:" select Whole Number
for "Data:" select between
for "Minimum:" type in 0
for "Maximum:" type in 100
Press OK

Excel Support Technician
www.canhelpyou.com

 

[Frank Kabel]

Hi
I tried it again and I was not able to enter 10% in this cell. Can you
post the exact formula you entered in the data validation dialog

 

[Graham]

Hi Frank,
I used the following,
=AND(ISNUMBER(C8),MOD(C8,1)=0,C8>=0,C8<=100)
When I pasted your one into the validation dialog I got a formula error
message. I perhaps incorrectly assumed that the semi-colon you had
......C8>=0;C8<=100) was an error and changed it to a comma. Was this my
mistake?

Graham

 

[Frank Kabel]

Hi Graham
no the semicolon was my fault -> changing this to a comma was correct

But still this should prevent al percentage entrie between 0% and 99%
(though you will be able to enter 100%)

 

[Graham]

Hi Frank,
Sorry to be a pain. If I enter 10 in the cell I get 10. If I enter
alphanumerics they get refused as they should be. If I enter 10%, ie the
number 10 followed by the % sign, I get 0.1 in the cell.

Graham

 

[Frank Kabel]

Hi
no problem
Though the formula should (as it does for me) prevent an entry of 0.1
or 10%.

 

[Graham]

Works from 1% to 99% but accepts 100% or 0%
Graham

 

[Graham]

Thanks for your perseverence and patience Frank. I will work away at it as I
am sure it will be a mistake somewhere on my part.

Graham.

 

[Frank Kabel]

Hi Graham
if you like, mail me your spreadsheet
(frank[dot]kabel[at]freenet[dot]de) and I'll have a look at it

 

[Frank Kabel]

Hi Graham
no chance to prevent this entry as these ar both valid numbers (0 and 1
respectively). Just a different format

 

[Graham]

Thanks for the offer Frank, but I have now got it to work as I was
preparing a sheet for you, so I must have been doing something stupid
somewhere along the line.
It now does everything you said it would. Many thanks.

Kind Regards
Graham

Hi Graham
if you like, mail me your spreadsheet
(frank[dot]kabel[at]freenet[dot]de) and I'll have a look at it

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks for your perseverence and patience Frank. I will work away at
it as I am sure it will be a mistake somewhere on my part.

Graham.