Forum Discussion
Lowest Common Multiple
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.
ESAM_HASHIM , you are welcome. But the link is on post, not on video. Just in case here is the summary
https://exceladept.com/determining-the-least-common-multiple-in-excel/
Hello SergeiBaklan
1. Actually I opened all your links by my mobile device before I discover a fact (Through Chat season with Norton support) that I must turn on VPN feature in my Windows PC
2. Thank you to provide me the article shown in your link that I will keep it as a great reference for any thing related to LCM formula's in Excel
Best regards
ESAM_HASHIM So I used the technique cited by SergeiBaklan in a REDUCE function and it works nicely on sheet1 and fails on sheet2. So I modified it to return the max LCM it finds:
=REDUCE(1,A1:A100,LAMBDA(p,q,IFERROR(p*q/GCD(p,q),p)))then you can see how it gets up to 4.1e+16. Then I did another version using SCAN so you can see how quickly it got there (row 30). In fact row 31 is the exact same number but it fail because using 4.1e+16 is too big for the function I assume.
I don't know that this is helpful but thought you might like to see.
- Hello m_tarler
Thank you very much for spending part of your valuable time to find the appropriate solution to an issue in finding the unique LCM number for a wide range of a numeric argument numbers. Perhaps third option through prime numbers could help to exceed the limit, not sure.
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.