Limit Values

  • Thread starter Thread starter Noepie
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top