data validation custom criteria formula question

  • Thread starter Thread starter snsyg
  • Start date Start date
S

snsyg

hello,

in the custom criteria formula for data validation, i have input the
following formula:

=OR((F6)="X",IF(ISNUMBER(F6),(F6)>-1))

the formula works just fine, except i want to allow only whole numbers
greater than -1. this formula allows numbers to be input with decimals
and produces no error message.

is there a way to resolve this?

thanks for any help!

karen
 
Hi
try
=OR(F6="X",AND(ISNUMBER(F6),F6>-1,MOD(F6,1)=0))
this will allow either 'X' or a whole number >-1
 
hi frank,

thanks for the reply, but....the formula you gave to me only partially
works. i am not familar with the mod function so i cannot figure out
what is wrong.

i get an error message for negative numbers and numbers entered with a
decimal, which is correct, but i also get an error message when i input
an "x", which is not correct.

i've checked a number of times to make sure i entered the formula as
you had sent and i have it correct in that respect.

any ideas what might be wrong?

also, just exactly what does the "mod" function do and what do the
numbers mean that are associated with it?

thanks for your help!

karen
 
Hi
no that was my fault. If you enter a text value (e.g. 'X') the MOD
function will throw an error and therefore 'X' is not accepted. change
the formula for the data validation to
=IF(ISNUMBER(F6),AND(F6>-1,MOD(F6,1)=0),F6="X")

The MOD function return the rest of a division. The first parameter is
divided by the second one (e.g. F6/1) and only the rest of this
division is returned. another example: MOD(3,2) will return 1
 
frank,

clever!

everything is working now. many thanks!!

this spreadsheet is turning into something decent, but i couldn't hav
done it without your help.

kare
 
Back
Top