Forum Discussion
Lowest Common Multiple
It is often very easy to apply the familiar FORMULA (=LCM(A1:A100)to find Lowest Common Multiple for a long wide numbers that included in a column as shown in sheet1
But sometimes I find it's so difficult to apply the above formula to another numbers as shown in sheet2
So I ask about the best formula for such instance that shown in sheet2
SergeiBaklan Well I took on the challenge and got somewhere with it. (attached). To do this I created a few Lambda functions:
PrimeFactors256 => returns an array with # of times each of the first 256 prime #s are a factor in the value and the 257th value is either a 1 indicating all the factors were found or the value of the 1 remaining prime factor. Since the 256th prime# is 1619 this will effectively find the Prime Factors for any number up to 1619^2.
PrimeFactors => uses PrimeFactors256 but then appends the actual prime numbers and filters the list to only those non-zero and essentially just a nicer/readable output for PrimeFactors256
LCM_prime => uses Prime Number technique to find the LCM. So this will use PrimeFactors256 and loop through all values inthe array keeping the max repeats of each of the 1st 256 primes and then tack on a unique list of primes above the 256th (those will always be a max of 1 for the valid range) and at the end basically multiple them all together.This function worked on sheet1 and will "work" on the sheet 2 BUT clearly is outputting a value that is affected by excel's limited number of significant digits. So assuming it has a good list of primes we could go down that road of 'big number calculation' again. I'm just happy to get the function to work in what i believe is a pretty efficient manner.
This, from Microsoft's documentation on the LCM function, may give you the answer:
In other words, the series of numbers in your example yields a value greater than 2^53
Thank you very much mathetes for your endeavors especally for the great info in the last paragraph
Here Determining The Least Common Multiple In Excel - ExcelAdept are variants for LCM calculation. Perhaps #3 could work with big integers. Will try with formula when have more time, that's not 5 minutes job. At least for me.
Thank you SergeiBaklan for your responding
I would like to inform you that the link did not open, unfortunatelySorry, I have no idea why you are not able to open the link. In my case it works if I click on it in above post.
It's tempting to create a recursive Lambda to obtain the LCM using the table method. My concern would be the number of iterations needed to obtain the LCM and Excel's ability to display potentially a very, very large number.
The juice may not be worth the squeeze.Patrick2788
I try to delet all the similar numbers in Sheet2 so that the result was the same when apply LCM formula, the reason is as mathetes say because the LCM number is greater than 2^53I am sorry that I haven't an idea about recursive Lambda
Encouraged by SergeiBaklan's suggestion, I have written some VBA code (including two custom functions) that determines the prime factors of a number, and the LCM for a pair of those factors. The code is not sufficiently robust at this time ("not ready for prime time"), so I have not included it. I will share it later.
See the attached workbook for initial results. I started by including just the unique numbers, assuming that my code would run slowly, but performance does not seem to be a problem so far.
