• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 006 - What's the largest number you can confirm is a prime?

Luke M

Excel Ninja
shrivallabha,

The problem is, at 1E+15, XL start dropping significant digits. It can't display this:

1,000,000,000,000,001

In your linked article, this is shown under the Number Precision category.


Since it no longer is retaining the digits from the ones value, all numbers become even, and no more primes can be found. The largest prime that XL can find is:

999,999,999,999,989
 

pedrowave

New Member
By chance, a day before jeffreyweir started this formula challenge I wrote a post in my blog entitled Prime factorization decomposition



Today just I'm reading about this challenge and I want to contribute with these two array formulas:

Code:
=IF(OR(NOT(ISNUMBER(A1)),A1<2,ISBLANK(A1)),0,A1*(SUM(--(FLOOR(A1,ROW(INDIRECT("1:"&INT(SQRT(A1)))))=A1))=1))
Code:
=(MOD(MOD(A1,2^16),2)=0)*2+(MOD(MOD(A1,2^16),2)<>0)*MIN(IF((A1-(INT(A1/(ROW(INDIRECT("2:"&INT(ROUNDDOWN(SQRT(A1)+1,0)/2)))*2-1))*(ROW(INDIRECT("2:"&INT(ROUNDDOWN(SQRT(A1)+1,0)/2)))*2-1)))=0,0,A1)+ROW(INDIRECT("2:"&INT(ROUNDDOWN(SQRT(A1)+1,0)/2)))*2-1,A1)
First formula length is 108 and works until 1099513724928
Last formula length is 254 and works until 4398050705408
 

jeffreyweir

Active Member
Great stuff, Petrowave. So the largest prime you can check is this:
4398050705407

My formula can process this in about 5 seconds:
122816065582079

And I have done numbers as large as this:
202005700000123

...but I often get an 'out of resources' error.
 
Top