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

Luke M

Excel Ninja
Staff member
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

Excel Ninja
Luke,

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

pedrowave

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

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.

pedrowave

New Member
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.