Excel excel problem, prime numbers :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 excel problem, prime numbers

mwilliams232
Welcome to the Board

Joined: 09 Dec 2002
Posts: 2

Status: Offline

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.

Mon Dec 09, 2002 9:22 am

Ken.Zeng
Welcome to the Board

Joined: 03 Dec 2002
Posts: 1

Status: Offline

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 ]

Mon Dec 09, 2002 9:33 am

mwilliams232
Welcome to the Board

Joined: 09 Dec 2002
Posts: 2

Status: Offline

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

thanks

Mon Dec 09, 2002 9:41 am

Richie(UK)
MrExcel MVP

Joined: 18 May 2002
Posts: 2425
Location: Worcester, England
Flag:

Status: Offline

Hi Matt,

Paste this into a general module in the workbook you are dealing with.

```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```

Then to see if the value in A1 is prime, type "=prime(A1)" in your chosen cell.

HTH

_________________
««« ¤ Richie ¤ »»»

Mon Dec 09, 2002 10:14 am

Andrew Poulsom
MrExcel MVP

Joined: 22 Jul 2002
Posts: 8180

Flag:

Status: Offline

Here are two more custom functions:

code:

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.

Mon Dec 09, 2002 10:32 am

.

Joined: 15 Feb 2002
Posts: 13745
Location: The Hague
Flag:

Status: Offline

And more here...

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

Mon Dec 09, 2002 11:56 am
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum