dividing cells by 15 and marking equal divisions

[janus67]

I have a column of numbers for example from A1 to A250 etc and I wish to mark somehow when the number is divisible by 15 exactly
so you could say I want to sort & mark the numbers divisible by 15 only

ie ColA1/15 to ColA14/15 the answer would be NIL as there's a remainder so no mark

ColA15 /15 =1 ( that would mean marking the cell somehow( or another cell , the simplest way)

Col16/15 to Col29/15 answer is nil again ( no mark, not divisible by 15 equally)

Col30 /15 =2 -- again a mark made
etc
MOD15 would divide the cells ok, but how to mark the cells divisible by 15?
I'd appreciate any help or advice here

thanks
Keith

 

[Tom Urtis]

Cannot tell the extent of what you are actually asking, it sounds like you only want a way to determine if numbers (for example) in column A are divisible by 15.

This True / False formula would do that, in B1 for example and copied down, or as a conditional formula rule:

=MOD(A1,15)=0

 

[janus67]

thanks Tom
trying to clarify the question -- my worksheet has hundreds of rows and a number of columns
in my example for simplicity, I just said A1 to A250
I placed your suggestion =MOD(A1,15)=0 in B1 and got a #false result

your idea sounds good--copy the formula into all cells and I'll either get a "number" or an
"0" result ( or a blank)
that would be great -- easy to spot

did I enter it wrong? or does it need a bit of tinkering?
sorry for the slow reply, I'd forgotten to update me mail address
thanks Keith

 

[Tom Urtis]

Either you entered the formula wrong

or

you entered it properly and A1 does not contain a number

or

something else is going on that is interfering with the process.

Take a close look at what really *really* is in cell A1. I would imagine you'd have received a #VALUE and not #FALSE like you said, so something fishy is happening, as the formula does work.

 

[janus67]

you were correct -- the column I copied included hidden decimals too
a new column of integers works fine

is there a simple way to reduce the clutter of many FALSE notes to see the TRUE one
can either the TRUE or FALSE one be "blank" or so to stand out better?

 

[Peg F]

Hi,

=IF(MOD(A1,15)=0,"TRUE","")

Will show true for all the true statements and will show blanks for all the false statements.

Peg

 

[janus67]

thanks Peg, works a charm

 

[janus67]

back again
I found that this formula , =IF(MOD(A1,15)=0,"TRUE","") , won't work in numbers with a decimal so I thought I'd use TRUNC to cut off the decimals.
sounded simple -- but I couldn't figure how to combine both formulas, so I made it a 2 step job, ok with me
then I found TRUNC wouldn't work "in" the same cell, it had to be another cell
so I made another column next to Col A which is now ColB-- truncated the cells in ColA into ColB -- all going well.
then as I have no further use for ColA , I deleted it --- big problems-- I didn't realise the Truncated cell in colB (eg B1)
was not an "answer" but that it only contained a formula that referred back to ColA ( A1) that I'd now deleted.

could someone advise please how I can Truncate the decimals in A1 into another " standalone " cell as it apparently must be, and then delete the then unnecessary original A1

thanks Keith

 

[Peg F]

Hi Keith,

I've modified my formula to include the trunc function

=IF(MOD(TRUNC(A1,0),15)=0,"TRUE","")

Hope this helps.

Peg

 

[janus67]

thanks Peg, it works beautifully