Excel Formula for finding a divisable match

gordonfitch

New Member
Joined
Sep 17, 2006
Messages
5
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Sep 17, 2006
Messages
5
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,021
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 17, 2006
Messages
5
Hello Peter

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

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top