rounding

Y

ynissel

is there a function I can write to check if a different cell is rounded to the
nearest 1/8th or can I make validation make sure the cell is entered to an
1/8th
 
N

N Harkawat

go to data--> validation-->Custom
and enter the following
=MOD(A1,0.125)=0
Now any time you enter values in cell A1 that is not a 1/8 it will give an
error message

Or if you want a function to check
=if(mod(a1,0.125)<>0,"Not 1/8th","1/8th")
 
Y

ynissel

Sorry I wasnt clear - I dont mean exactly 1/8 I mean rounded to an 1/8 (ie,
..125,.25,.375 etc...)
 
R

Ron Rosenfeld

Sorry I wasnt clear - I dont mean exactly 1/8 I mean rounded to an 1/8 (ie,
.125,.25,.375 etc...)
Click to expand...

What, exactly, did the recommended formula DO when you tried it?

It seems to work fine, here.

You did try it first, didn't you?


--ron
 
Y

ynissel

yes I did :)
It only allowed a value of exactly .125 !
I want to check if its rounded to the nearest 1/8th
Thanks
 
Y

ynissel

I got it to work using =IF(FLOOR(B4,0.00125)=B4,"rounded properly","not
rounded to 1/8") but For some reason I cant get get it to work inconditional
formatting - this is what I wrote.
="FLOOR(B4,0.00125)=B4"
 
R

Ron Rosenfeld

I got it to work using =IF(FLOOR(B4,0.00125)=B4,"rounded properly","not
rounded to 1/8") but For some reason I cant get get it to work inconditional
formatting - this is what I wrote.
="FLOOR(B4,0.00125)=B4"
Click to expand...

Are you certain you are wanting the number to be rounded to the nearest 1/8 and
not to the nearest 1/8% ?????

Also, your formula would seem to only work if your value was rounded DOWN and
not rounded to the nearest (i.e. down if less than 1/2 way between, up if 1/2
way between or more).

Using your formula, and a value of 1.3 in B4, which is clearly not rounded to
the nearest 1/8th, I get a result of "rounded properly".

Once we have this straightened out, getting the CF formula to work will be
trivial.


--ron
 
Top