Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
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 Replace @mailinator.com with @tiscali.co.uk "nastech" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
Hi Nastech,
Possibly this may do it. =MOD(INT(I18),INT($J$8))=0 HTH Martin "nastech" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
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 "MartinW" wrote: Hi Nastech, Possibly this may do it. =MOD(INT(I18),INT($J$8))=0 HTH Martin "nastech" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
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" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "nastech" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
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 "MartinW" wrote: Hi Nastech, Possibly this may do it. =MOD(INT(I18),INT($J$8))=0 HTH Martin "nastech" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
ahhhh, guesse correct use of round: =ROUND(MOD(I17,$J$8),4)=0
seems to be getting it. thanks much "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "nastech" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to test for multiple of 5, or .5, .05, .005, .0005
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 "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "nastech" wrote in message ... 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 "MartinW" wrote: Hi Nastech, Possibly this may do it. =MOD(INT(I18),INT($J$8))=0 HTH Martin "nastech" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tukey`s multiple comparison test sotware | Excel Discussion (Misc queries) | |||
Test Cell For Multiple Criteria | Excel Worksheet Functions | |||
How do I keep the zeros...I need the numbe to be 0005...not 5?!?! | Excel Discussion (Misc queries) | |||
help - create a multiple choice test | Excel Discussion (Misc queries) | |||
Multiple-Test Conditional Formatting | Excel Discussion (Misc queries) |