excel problem, prime numbers

[mwilliams232]

hey,

does anyone know how to make an excel spreadsheet that inputs any number and in another cell outputs if the number is prime or not. thanks.

-matt w.

 

[Ken.Zeng]

U can use
countif
=IF(COUNTIF(Myrange,A1)=1,"Prime",COUNTIF(Myrange,A1))
Myrange=Sheet2!$A$2:INDIRECT("A"&COUNTA(Sheet2!$A:$A))
Input you NO.columnA
Input the above formula in cell A1
then paste down
This message was edited by Ken.Zeng on 2002-12-09 04:36

 

[mwilliams232]

when i input that formula into A1, it comes up as #NAME? do you know what is wrong?

thanks

 

[Richie(UK)]

Hi Matt,

Paste this into a general module in the workbook you are dealing with.
<pre>Function Prime(TestRng As Range)
'code by Tom Ogilvy (modified from original by Myrna Larson)
'slight modification by RPS to accept range
Dim PrimeCnt As Long
Dim y As Long
Dim x As Long
Dim i As Integer
Dim Flag As Boolean
Dim Primes() As Long
Dim NumStop As Double
Dim TestNum As Long
ReDim Primes(1 To 2)

TestNum = TestRng.Value

NumStop = Sqr(TestNum)
If TestNum = 1 Or _
TestNum = 2 Or _
TestNum = 3 Or _
TestNum = 5 Then
Prime = True
Exit Function
End If
Primes(1) = 2
Primes(2) = 3
PrimeCnt = 2
x = 3

Do
x = x + 2
For y = 3 To Sqr(x) Step 2
If x Mod y = 0 Then GoTo NoPrime1
Next y
PrimeCnt = PrimeCnt + 1
ReDim Preserve Primes(1 To PrimeCnt)
Primes(PrimeCnt) = x
NoPrime1:
Loop Until Primes(PrimeCnt) > NumStop

For i = LBound(Primes) To UBound(Primes)
If TestNum Mod Primes(i) = 0 Then
Debug.Print i, Primes(i), TestNum / Primes(i)
Prime = False
Exit Function
End If
Next
Prime = True
End Function</pre>
Then to see if the value in A1 is prime, type "=prime(A1)" in your chosen cell.

HTH

 

[Andrew Poulsom]

Here are two more custom functions:

Option Explicit

'' ***************************************************************************
'' Purpose  : Test whether the argument is a PRIME number
'' Written  : January 98 by Andy Wiggins - Byg Software Ltd
'' Notes    : Returns TRUE or a zero length string
''
Function IsPrime(vlTestNumber As Long)
''Application.Volatile                                ''Not essential for this demo
Dim vlCount As Long                                 ''Declare some variables
Dim vlHalf As Long

    vlHalf = vlTestNumber / 2 + 1                   ''By using a LONG we get an integer result, which is what we want
    For vlCount = 2 To vlHalf                       ''Loop until we get to vlHalf
        If (vlTestNumber Mod vlCount) = 0 Then      ''If we get a ZERO result in this test, the number is not a prime ..
            IsPrime = ""                            ''.. so return an empty string ..
            Exit Function                           ''.. and leave the function
        End If
    Next
    IsPrime = True                                  ''If we get here, the number is a prime, so return TRUE
End Function

'' ***************************************************************************
'' Purpose  : Test whether the argument is a PRIME number
'' Written  : January 98 by Andy Wiggins - Byg Software Ltd
'' Notes    : Returns TRUE or FALSE
''
Function IsPrimeBool(vlTestNumber As Long) As Boolean
''Application.Volatile                                ''Not essential for this demo
Dim vlCount As Long
Dim vlHalf As Long

    vlHalf = vlTestNumber / 2 + 1                   ''By using a LONG we get an integer result, which is what we want
    For vlCount = 2 To vlHalf                       ''Loop until we get to vlHalf
        If (vlTestNumber Mod vlCount) = 0 Then      ''If we get a ZERO result in this test, the number is not a prime ..
            IsPrimeBool = False                     ''.. so return FALSE
            Exit Function                           ''.. and leave the function
        End If
    Next
    IsPrimeBool = True                              ''If we get here, the number is a prime, so return TRUE
End Function

And here is an array formula:

=OR(A1=1,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

After typing it press Ctrl+Shift+Enter not just Enter.

 

[Aladin Akyurek]

And more here...

http://www.mrexcel.com/board/viewtopic.php?topic=17861&forum=2

 

[sat.wnz]

Hi
I am stish kumar singh working in ms excell i have efficient way to find out prime number in excell sheet.

 

[sat.wnz]

I can find out prime number in excell up numerical range of excell sheet.if some one intrested i can send them the way.

 

[sat.wnz]

hey,

does anyone know how to make an excel spreadsheet that inputs any number and in another cell outputs if the number is prime or not. thanks.

-matt w.

yes i can do this.i hv formula to do this.

 

[MartinL]

And here is an array formula:

=OR(A1=1,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

After typing it press Ctrl+Shift+Enter not just Enter.

Andrew: Isn't 2 a prime number

Martin