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

[Luke M]

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

 

[shrivallabha]

Luke,


Yep, you are correct about largest 'prime' number.

 

[pedrowave]

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:

=IF(OR(NOT(ISNUMBER(A1)),A1<2,ISBLANK(A1)),0,A1*(SUM(--(FLOOR(A1,ROW(INDIRECT("1:"&INT(SQRT(A1)))))=A1))=1))

=(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]

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.

 

[pedrowave]

Thanks jeffreyweir for your heartfelt words.

As you saw, I included a picture in Spanish in the above message, but you can download the English version here in my SkyDrive, and check those formulas in action. To test large numbers, use Excel 2010 version: DescomposiciónFactorial-PW5.xlsx only made with formulas!

To calculate prime factors automatically enter ¶ (ALT + 0182) character in cell C11.