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

[nastech]

hi, trying to test column of numbers for being a multiple of ~ 5
where this is used in a conditional format that tests a column of sequential
numbers.

cond. format works for the following formula's: (where):

$I$7: start level for sequential numbers; works for .20, cond. format
does not work for entry of 2.00 (1st 3 sequence of mult of 5 do work in the
cond. format: 2.00, 2.05, 2.10; but stops there,

PROBLEM: 2.15 does not hilite with cond. format formula below.

(KEY?): $I$8 will change, but is set to .01
$J$8: set to .05 (for .20 setting in $I$7 works; for 2.00 & .10 items
work, .05 does not for 2.15, 2.20, 2.25.. and after)

external formula that gets sequential numbers
=IF(AND(H17="",OR(I16="",$I$7="",$I$8="")),"",IF(H17="x",$I$7,I16+IF($I$8="",1,$I$8)))

Conditional Format formula:
=OR(AND(ROUND(MOD(I17,$J$8),4)=0,I17<>""),H17="z")

 

[nastech]

ps.

PROBLEM: Conditional Format formula:
=OR(AND(ROUND(MOD(I17,$J$8),4)=0,I17<>""),H17="z")

tried instead of I17, RIGHT(I17,2) not working
tried instead of $J$8, $I$8*$J8 wonder if something to that end is the
answer.

 

[Joel]

I found two problems with your formula

Here is my correction
=OR(AND(MOD(INT(100*I1),INT(100*$J$8))=0,I1<>""),H2="z")

Problem 1 - The division of fractional number was giving a very samall
remainder when you divided 2.0 / 2.0 (1.0E^-16). So converting the proble to
an integer by multiplying both the numberator and denominator by 100 and
using the INT function to get an integer solved this issue

Problem 2 - To get every 5th number I then used 25 (.25 * 100) instead of 5
(.05 * 100).

 

[nastech]

hi, thanks for the reply. didn't think would get response, i ask what seem
to be funny questions sometimes. (couldn't imagine why whole numbers were
returning fractions, thought going crazy).

I was looking at it today and found an answer myself. will look at yours,
but not that quick at some of this; if you think yours is better let me
know, but a guesse that using number that doesn't have to be translated (5
or .25, same?..) but saw same that if do have fraction find a ROUND down
(na), FLOOR() seems to be working:

$I$8: .01
main formula (not cond. format), added FLOOR() & 2nd $I$8:
=IF(AND(H17="",OR(I16="",$I$7="",$I$8="")),"",FLOOR(IF(H17="x",$I$7,I16+$I$8),$I$8))

$J$8: .05 (every nickel change)
2nd cond. format:
=OR(AND(ROUND(MOD(I17,$J$8),2)=0,I17<>""),H17="z")
this allowed all other cells that used similar cond. forat to hilite as
appropiate.


1st cond. format:
H17<>"z": used to manually hilite that row, rest following is unlrelated
cond. format
New neat trick: always wondered how to get jumbo line for conditional
format border: by using NOT() concept in 1st cond. format, & using a single
line border to block out the heavy line, if "NOT" true.
=AND(NOT(ROUND(MOD(I17,$J$8),2)=0),H17<>"z",IF(I17="","",I17<$I$3))

3rd cond. format:
=0=0
for just solid line border, remaining non-condition to block heavy border.


question: do you think round to 2 places is appropiate?
thanks.

 

[nastech]

found 1 problem, going down the sheet alittle, I delete line 40, that looks
like:
=IF(AND(H40="",OR(I39="",$I$7="",$I$8="")),"",
FLOOR(IF(H40="x",$I$7,I39+$I$8),$I$8))

turns into: (including all cells after) when I delete lines, the cell
referencing the line before messes up:
=IF(AND(H40="",OR(#REF!="",$I$7="",$I$8="")),"",
FLOOR(IF(H40="x",$I$7,#REF!+$I$8),$I$8))

when I add lines, the cell reference seems to start over, would again have
to repaste this column to correct.

 

[nastech]

I'm going to guesse: use of OFFSET()
will repost with answer. thanks

 

[nastech]

this seems to work
=IF(AND(H17="",OR(OFFSET(I17,-1,0)="",$I$7="",$I$8="")),"",FLOOR(IF(H17="x",$I$7,OFFSET(I17,-1,0)+$I$8),$I$8))

 

[Joel]

Using floor was a good idea. I knew there was a function like that, but
didn't remember which one it was. It was just quicker to multiply by 100 and
use INT. Sometimes it pay just to do things the quick way than the right
way. In this case, looking up the function name would of taken time.

 

[David Biddulph]

It's worth remembering that Excel help is good at telling you about related
functions. From help for INT the "See also" link would have shown you FLOOR
[and others].

 

[nastech]

hi, thanks both. although my formula generally works, it does not seem to be
doing what expected for the cond. format for values less than 1 (i.e.
dollars & cents)
for cond. format:
=OR(H20="z",AND(ROUND(MOD(I20,$J$8),2)=0,I20<>""))

(maybe could just use externally & drag down a column to see try / falses)
trues or "hilites" are funny. choose start value .085 (8.5 cents) for
start,
increments of: .005 & (cond. format) hilite increments: .01

you would think hilite would skip all .005 (every other) values.
getting hiltes: .09, .10, .105, .11, .12, .13, .14, .145, .15
getting .005 every third penny. don't quite get that.
my work around might include within the AND($I$7>=1
and put a new variable in for less than $1, not sure any ideas?????? thanks
guessing something wrong with using ROUND()

It's worth remembering that Excel help is good at telling you about related
functions. From help for INT the "See also" link would have shown you FLOOR
[and others].
--
David Biddulph

Using floor was a good idea. I knew there was a function like that, but
didn't remember which one it was. It was just quicker to multiply by 100
and
use INT. Sometimes it pay just to do things the quick way than the right
way. In this case, looking up the function name would of taken time.

 

[nastech]

hi, sorry, keep answering my own questions, just learning these functions.
answer, guesse had asked about earlier, is to not round to 2 places, but if
working with a number such as: .0001

answer is to ROUND(x,4) to 4 places.
thanks

hi, thanks both. although my formula generally works, it does not seem to be
doing what expected for the cond. format for values less than 1 (i.e.
dollars & cents)
for cond. format:
=OR(H20="z",AND(ROUND(MOD(I20,$J$8),2)=0,I20<>""))

(maybe could just use externally & drag down a column to see try / falses)
trues or "hilites" are funny. choose start value .085 (8.5 cents) for
start,
increments of: .005 & (cond. format) hilite increments: .01

you would think hilite would skip all .005 (every other) values.
getting hiltes: .09, .10, .105, .11, .12, .13, .14, .145, .15
getting .005 every third penny. don't quite get that.
my work around might include within the AND($I$7>=1
and put a new variable in for less than $1, not sure any ideas?????? thanks
guessing something wrong with using ROUND()

It's worth remembering that Excel help is good at telling you about related
functions. From help for INT the "See also" link would have shown you FLOOR
[and others].
--
David Biddulph

Using floor was a good idea. I knew there was a function like that, but
didn't remember which one it was. It was just quicker to multiply by 100
and
use INT. Sometimes it pay just to do things the quick way than the right
way. In this case, looking up the function name would of taken time.

:

this seems to work:
=IF(AND(H17="",OR(OFFSET(I17,-1,0)="",$I$7="",$I$8="")),"",FLOOR(IF(H17="x",$I$7,OFFSET(I17,-1,0)+$I$8),$I$8))

:

I'm going to guesse: use of OFFSET()
will repost with answer. thanks