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(H 17="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")

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.


"nastech" wrote:

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(H 17="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")

I found two problems with your formula

Here is my correction
=OR(AND(MOD(INT(100*I1),INT(100*$J$8))=0,I1<""),H 2="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" wrote:

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.


"nastech" wrote:

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(H 17="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")

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="")),"",FLOO R(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.

"Joel" wrote:

I found two problems with your formula

Here is my correction
=OR(AND(MOD(INT(100*I1),INT(100*$J$8))=0,I1<""),H 2="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" wrote:

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.


"nastech" wrote:

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(H 17="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")

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" wrote:

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="")),"",FLOO R(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.

"Joel" wrote:

I found two problems with your formula

Here is my correction
=OR(AND(MOD(INT(100*I1),INT(100*$J$8))=0,I1<""),H 2="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" wrote:

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.


"nastech" wrote:

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(H 17="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")

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