dividing cells by 15 and marking equal divisions

janus67

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 7, 2011
Messages
46
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

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

thanks Peg, works a charm
 

janus67

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 7, 2011
Messages
46
Hi Keith,

I've modified my formula to include the trunc function

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

Hope this helps.

Peg
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,446
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top