# dividing cells by 15 and marking equal divisions

#### janus67

##### Board Regular
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
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
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
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

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
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

thanks Peg, works a charm

#### janus67

##### Board Regular
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
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

##### Board Regular
thanks Peg, it works beautifully

Replies
33
Views
2K
Replies
4
Views
223
Replies
1
Views
633
Replies
7
Views
856
Replies
1
Views
1K

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?

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