Need to set up formula to create mutliples of two numbers

[Julie P.]

Hi, I would like to find all the common multiples of the numbers 64 and 96.
For example, 192 is a multiple of both 64 and 96.

This is what I have done so far:

http://mallology.lunarpages.com/gallery-multiples/gallery-multiples.jpg

What I have to do is first create a column (column A) with the sequence 1,
2, 3, 4, 5, ..., using the formula "=A1+1", then fill down.

Then in B1, I enter "64". Right below this cell, I type:

=$B$1*$A2

then I copy down.

Then in C1, I enter "96". In C2, I fill right from B2, yielding:

=$B$1*$A2, and then I change "B" to "C" to get:

=$C$1*$A2

then I go down and highlight any common multiples I am able to pick out in
the list, as seen in the screenshot above.

Surely, there must be an easier way to do this?

Thanks for any help!

Julie

 

[Frank Kabel]

Hi
for the least common multiple use the array formula:
=number2*MATCH(0,MOD(number2*ROW(INDIRECT("1:"&number1)),number1),0)

or the LCM function from the analysis Toolpak Addin

 

[Julie P.]

Hi
for the least common multiple use the array formula:
=number2*MATCH(0,MOD(number2*ROW(INDIRECT("1:"&number1)),number1),0)

or the LCM function from the analysis Toolpak Addin

Thanks Frank! These sound complex! I was looking for one that would either
just give me a list of all common multiples of two numbers; or, if that were
not possible, a formula that would simply list all multiples of one number,
like:

2 4 6 8 10 12 etc.

then I would do the same for the other number, and then manually find all
the common multiples.

J.

 

[Ron Rosenfeld]

Surely, there must be an easier way to do this?

You could try a VBA solution:

=====================================
Function CommonMultiple(num1 As Long, num2 As Long, Size As Long)
Dim i As Long
Dim num1Mults() As Long
Dim res() As Long

ReDim num1Mults(1 To Size)
ReDim res(0)

For i = 1 To Size
num1Mults(i) = num1 * i
Next i

For i = 1 To Size
If num1Mults(i) / num2 = Int(num1Mults(i) / num2) Then
res(UBound(res)) = num1Mults(i)
ReDim Preserve res(UBound(res) + 1)
End If
Next i

CommonMultiple = res

End Function
==========================

To enter a UDF, <alt><F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code into the window that appears.

This is an *array* formula. Num1 and Num2 are the two numbers you wish to find
a common multiple (eg. 64 and 96) and Size is the range over which you want to
test multipliers (e.g. if you enter 100, multipliers from 1 to 100 will be
tested).

To use it, first select a range of cells large enough to encompass the expected
output.

In the formula bar:

if the range is a row, type =CommonMultiple(num1, num2, Size)

if the range is a column, type =TRANSPOSE(CommonMultiple(num1, num2, Size)

Then hold down <ctrl><shift> while hitting <enter> and you should see all the
results.


--ron