ThreeWave Testing Prime Numbers In Excel

This page describes how to use a formula to test if a number if a Prime number or a Prime Twin.
ShortFadeBar

Prime numbers do not have much application in most real world computing, especially the sort done in Excel. However, prime numbers have been a source of fascination to mathematicians since the earliest dawn of mathematics. (I was a Math major in college and thus have an interest in prime numbers for their own sake.) This page looks at two formulas for testing when a number is a prime number.

A Prime Number is a positive whole number that can be evenly divided only by one and itself. Division by any other number less than the prime number results in a remainder. For example, 7 is a prime number because it is evenly divisible by only 1 and 7. The number 12 is not a prime number because it is evenly divisible by 1, 2, 3, 4, and 6. As you continue in the positive direction on the Real Line, the density of primes (that is, how many primes exist in a set of numbers between N and N+K) decreases. In the positive direction, primes become more scarce. One of the central theorems of Number Theory provides that the density of primes is always greater than a known number for the first incomprehensively large number of integers. That is, the density of prime numbers will never fall below a specific number.

Even though the number 1 is divisible only by 1 and itself, it is not considered prime due to some rather advanced mathematical theory.

You can download a workbook illustrating these formulas.

SectionBreak

Is A Number A Prime?

The formula below will test the number in cell C8 and return the word prime if it is prime or the string not prime if the number is not prime.

=IF(OR(C8=2,C8=3),"prime",IF(AND((MOD(C8,ROW(INDIRECT("2:"&C8-1)))<>0)),"prime","not prime"))

This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. See the array formulas page for much more detail about array formulas. Note that the formula will not work properly if you do not enter it with CTRL SHIFT ENTER. This formula will work with numbers up to 268,435,455, after which Excel's ability to handle the intermediate array fails.

SectionBreak

Prime Twins

This section describes a formula to test whether a number is not a prime, is a prime, or is a prime twin.

Prime Twins are pairs of numbers such that N and N+2 (or, equivalently N and N-2) are both Prime. For example, 17 and 19 are prime twins because 17 is prime and 17+2=19, is also prime. On the other hand, 19 and 23 are not prime twins because, while they are both prime and there are no primes in between them, they are more than 2 units apart. Prime twins are even more rare than prime numbers, but it has been shown in Number Theory the density of prime twins will never fall below a certain known number.

The function below will return prime twin if the value in C15 is a prime and either C15-2 or C15+2 is a prime. If C15 is prime but neither C15-2 nor C15+2 is prime, the result is prime. If C15 is not a prime, the result is not prime. Like the formula above, this is an array formula so you must enter it with CTRL SHIFT ENTER. For readability here, the formula is split across several lines. In Excel, you will need to join these lines together.

=IF(OR(C15=1,C15=3),"prime twin",IF(AND((MOD(C15,ROW(INDIRECT("2:"&C15-1)))<>0)),
     IF(OR(AND((MOD(C15-2,ROW(INDIRECT("2:"&C15-3)))<>0)),
     AND((MOD(C15+2,ROW(INDIRECT("2:"&C15+1)))<>0))),"prime twin","prime"),"not prime"))

This formula will work in Excel 2003 and earlier with numbers up to 65,536. In Excel 2007, it will work with numbers up to 1,048,577. In Excel 2007, you can use the formula below to work with numbers up to 268,435,455. 

=IF(C15=3,"prime twin",IF(AND((MOD(C15,ROW(INDIRECT
     ("2:"&INT(SQRT(C15)))))<>0)),IF(OR(AND((MOD(C15-2,ROW(INDIRECT("2:"&
    INT(SQRT(C15)))))<>0)),AND((MOD(C15+2,ROW(INDIRECT("2:"&INT(SQRT(C15)))))<>0))),
    "prime twin","prime"),"not prime"))

You can download a workbook illustrating these formulas.

This page last updated: 6-June-2011

-->