Microsoft Excel Prime Numbers

Prime numbers were one of those topics likely covered early in your scholastic life. Here’s the fastest refresher course you’ll ever have:

Prime numbers are those numbers that can be divided only by themselves, and the number 1. It’s a universal mathematical law that these numbers exist and the get considerably more rare the farther you get from zero.

If you saw the movie “Contact” with Jodie Foster, you may remember the scene where otherworldly beings are communicating with Earth by transmitting a specific number of low-decibel “beeps” in prime number groups thus creating the automatic assumption that even aliens went to grammar school and learned prime numbers.

Our next function covers figuring out if a number is prime or not. As for a practical application for such a function – I really can’t think of one off of the top of my head, but I’m certain that a function that determines prime numbers has great use somewhere. If you happen to know of one, drop me a line.

Here’s our function:

Function IsPrime(ByRef rngVal As Range) As Variant
Dim ValPrime As Boolean
Dim x As Long
Dim ValGCD As Double
Dim Val As Double

Val = rngVal.Value

If Val = 1 Or Val < 0 Or Val <> Int(Val) Or IsEmpty(rngVal) = True Then
  IsPrime = "#VALUE"
  Exit Function
End If

ValPrime = True

For x = 2 To Val - 1
  If Val / x = Int(Val / x) Then
  ValPrime = False
  IsPrime = ValPrime
  Exit Function
  End If

Next x

IsPrime = ValPrime

End Function

This particular function checks first to see if the number is 1, negative, blank, or a decimal, and will return the #Value error if any of those are the case.

After pasting this function into a new or existing module, jump over to your worksheet and try it out. In cell A1, put in the number 4, in B1, place the formula:

=IsPrime(A1)

You should return “False” as the result. If you put the number 359 in cell A1, the IsPrime function returns True because the number 359 can only be divided by itself, and 1.

And there we have it! The function itself is fairly self-explanatory, it simply loops through every number from 2 to the number you are testing minus 1 in order to determine whether or not your number is divisible by anything other than 1 and itself.

Have fun finding a practical application for this interesting function!

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Microsoft Excel Prime Numbers