MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Prime Numbers

Posted by Chris D on January 09, 2002 1:51 PM

Hi all,

Does/can Excel (97) recognise prime numbers in any way ?

many thanks

Posted by Tom Urtis on January 09, 2002 2:22 PM

Here's a UDF that appeared on this board some time ago. I take no credit whatsoever for this; I wish I could remember who wrote it to give credit where it's due (you know who you are!).

Paste it into a module. Then, if for instance you are evaluating A1 as being a prime number or not, type the formula =PRIMENUMBER(A1) into B1 or wherever, and it will return a value of True or False.

Public Function PrimeNumber(ByVal iNumber As Integer) As Boolean
Dim i As Integer
PrimeNumber = True

Select Case iNumber
Case 0
PrimeNumber = False
Exit Function

Case 1 Or 2
Exit Function

Case Else
For i = 2 To iNumber - 1
If iNumber Mod i = 0 Then
PrimeNumber = False
End If

End Select
End Function

Tom Urtis

Posted by Mark W. on January 09, 2002 2:30 PM

I hope Chris doesn't need to check if 9.99999999999999E307 is prime! (nt)

Posted by Chris D on January 09, 2002 2:33 PM

checks for any remainder via MOD on all numbers between it and 2 ?

awesome lateral thinking in that code
thanks Tom

Posted by Chris D on January 09, 2002 2:35 PM

haha nope, I'm just an accountant :-) (NT)

Posted by Mark W. on January 09, 2002 2:38 PM

Posted by Chris D on January 09, 2002 2:50 PM

Sort of....... we have been tasked to reduce our stationery budget and I'm the main culprit via printing off reams and reams of stuff each day week month and quarter (control accounts and tax)

so I suggested rather than printing them, they are stored electronically, but my boss worried about audit trails and the lack of an ink signature

I was tryign to think of a way to save my files with unique, non-repeatable filenames or values in the file somewhere that could not be changed but proved they had been verified

I remembered somewhere about using the product of primes in generating a unique number that couldn't be reversed without a lot of number crunching on Crays....

Posted by Paul on January 09, 2002 3:35 PM