Limit Values

N

Noepie

Hello,

I have the following issue which I am not able to solve. I want to limit the
value possibilities of a cell to #,0 and #,5 For example: 8,5 or 7,0 but not
6,2 and so on. I tried to do it with validation but it doesn't work. Hope
anyone can solve this out for me.

Many thanks.

Noepie
 
M

muddan madhu

assumed the cell A1 need to limit the number to ( 0,5,7,8)

Go to data | validaton | allow:custom | formula : =OR
(A1=0,A1=5,A1=7,A1=8) | ok
 
N

Noepie

Hello,

Unfortunately, the solution results in invalid entries even when I put in 10
or 8,5 or another valid entry. Is there another way to try?

Kind regards,

Noepie
 
S

Shane Devenshire

Hi,

First rule of the newsgroups - post the answer!

For one thing you can't be sure that you got the best answer but more
importantly, the newgroups are provided for a world of user to refer to for
answers to solved problems.

Second, I am going to guess that you did not mean the numbers 0 and 5 but
number with 0 or 5 after the decimal point?

In which case the following works in most cases

=OR(MOD(A1,0.5)=0,MOD(A1,1)=0)

but because the MOD function is notorious for small errors in the last
decimal places, this can give the wrong results. Try 123456789 for example.
One alternative might be

=IF(ISERROR(FIND(".",A1)),TRUE,OR(MID(A1,FIND(".",A1)+1,16)="5",MID(A1,FIND(".",A1)+1,16)="0"))

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Top