Trying to test for multiple of 5, or .5, .05, .005, .0005

[nastech]

hi, in trying to find multiple of 5, (for conditional formatting purposes).
for multiple of 5 or .5, .05, .005, .0005 to mess it up: where
MOD(15,5)=0 works,

When I try to use external "calculated" cells for 15/5, FOR:
=MOD(I18,$J$8)=0 the remainder is not exactly 0., but
..00000000000000000433.., maybe there is another method? Tried FLOOR() or
ROUND() does not work, thanks
I18 calculated to: 15
J8: 5

 

[Sandy Mann]

I don't really follow what you are saying but I would think the the clue was
in:

I18 calculated to: 15

If I18 is a calculated value then it more than likely is not exactly 15 even
although it is showing as 15. Have you tried rounding I18 to zero decimal
places?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MartinW]

Hi Nastech,

Possibly this may do it.
=MOD(INT(I18),INT($J$8))=0

HTH
Martin

 

[nastech]

hi, it helped remind of INT, but did not quite work; not sure what problem
is.
yesterday formula for getting off zero answer, but would think that:
=MOD(I17,$J$8) where I17 = .15 and $J$8 = 5

MOD should get .15/5=.03 remainder 0? getting: .15
my problem may include some of the simple use of MOD etc. new to me, but it
seems straight forward. just trying to get a TRUE condition if I pick
multiple of 5 or 10. thanks.

aside from that INT() might negate the use of .05 in cell $J$8

 

[nastech]

hi, thanks, problem seems to be moving around alittle. although was hand
entering .15 in a different absolute cell, think my formula (not shown) was
doing a mistake.. won't post it as is garbage; where I am at:

hi, it helped remind of INT, but did not quite work; not sure what problem
is.
yesterday formula for getting off zero answer, but would think that:
=MOD(I17,$J$8) where I17 = .15 and $J$8 = 5

MOD should get .15/5=.03 remainder 0? getting: .15 when take away the =0
in: =MOD(I17,$J$8)=0

(am using a Custom Formatting for numbers... not sure would be prolem, but
that is: [>=10]#.0;[>=1]#.00;#.0000

my problem may include some of the simple use of MOD etc. new to me, but it
seems straight forward. just trying to get a TRUE condition if I pick
multiple of 5 or 10. thanks.

aside from that INT() might negate the use of .05 in cell $J$8

 

[Sandy Mann]

Mod does not work quite the way that you think that it does. From Help:

**************************************************
The MOD function can be expressed in terms of the INT function:

MOD(n, d) = n - d*INT(n/d)
**************************************************
The result of that calculation is 0.15 as the MOD() function is returning.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[nastech]

ok, see some of: where MOD(0.15,.05)=0 comes up true still is not for my
bring forward cells, breaking this down i see:

focus: =MOD(I17,$J$8) is comming to .0000
but when put =0 after the formula, and checking formatting / answer: am
getting:
-.00000000000000000138777... etc
(using format cells, number, decimal places: 30)

ahhhh, guesse correct use of round: =ROUND(MOD(I17,$J$8),4)=0
seems to be getting it. thanks much


how do I "generically"?? round to .0000

 

[nastech]

ahhhh, guesse correct use of round: =ROUND(MOD(I17,$J$8),4)=0
seems to be getting it. thanks much

 

[nastech]

thank you, I will keep note of that and review it.
The last part of the problem: Is there a way to make the divisor a sliding
scale? if the right question, to allow use of different divisors as stated /
including: 50, 5, .5 .05 .005 .0005

thanks