Excel Formula for finding a divisable match

[gordonfitch]

Can anyone assist? I would like to know how to write a formula that can search a selected column for a number that is divisable by another number found in a specified cell.

I have a table with 300 rows, each numbered 1:300, I have another cell with a specific number.

I need to be able to identify each number in a specified column which is exactly divisable by the specific number.

For example: If the sum of any cell in column A, containing a number which is exactly divisable by cell (eg F6 which might contain number 11), then another action is to be applied.

My problem is that I do not know how to write a formulae that is able to compare one cells contents with a column of other cells 1:300 in an attempt to identify each number in that column which is exactly divisable by the specified number.

Thanks - Gordon Fitch

 

[SydneyGeek]

Hi Gordon, welcome to the Board!

One option...
Assuming you want to scan Column A:
=MOD(A1,$F$6)=0

Fill down. Divisible numbers will have TRUE. You can apply a filter to isolate them.

Denis

 

[gordonfitch]

Helo Denis, Thanks for the reply.

So I take it that by applying this formula, that all numbers in the entire column selected - in this case 'A', will be scanned to identify all numbers that are divisable by the = number nominated?

Thanks - Gordon

 

[tusharm]

Why not test it yourself?

An alternative would be to use conditional formatting (Format | Conditional Formatting...) to highlight all cells in column A that meet the necessary requirement. Use the formula posted by Denis.

Helo Denis, Thanks for the reply.

So I take it that by applying this formula, that all numbers in the entire column selected - in this case 'A', will be scanned to identify all numbers that are divisable by the = number nominated?

Thanks - Gordon

 

[Peter_SSs]

gordonfitch

You indicated in your original post that if the number is exactly divisible, then another action would ensue. So, if your numbers are in column A and the test number is in F6, then I would modify Denis' suggestion to this. In B1 (copied down):
=IF(MOD(A1,$F$6)=0,your_other_action_here,"")

 

[gordonfitch]

Hello Peter

Thanks for that - I have now got if working as hoped. Thanks to you and Denis.