Prime Numbers

[XL_2K3]

Hi there:

Do you think any of you could help me to make this UDF so when a 0 (zero) or 1 (one) is inputted it retrieves an "X" or any other character as the output? (since 0 and 1 are not prime nor composite numbers).

Well here is the UDF i want to be adapted.

Function prime(num) 
    Dim j, k, remain As Integer 
    j = Int(num) 
    If j <> num Then 
      prime = "NonInt" 
      Exit Function 
    End If 
    j = Sqr(num) 
    For k = 2 To j 
     remain = num Mod k 
     If remain = 0 Then 
        prime = "NonPrime" 
        Exit Function 
     End If 
    Next k 
    prime = "Prime" 
End Function

Thanks a lot in advance!

 

[Andrew Poulsom]

Try:

Function prime(num)
    Dim j, k, remain As Integer
    j = Int(num)
    If j <= 1 Then
        prime = "X"
        Exit Function
    End If
    If j <> num Then
      prime = "NonInt"
      Exit Function
    End If
    j = Sqr(num)
    For k = 2 To j
     remain = num Mod k
     If remain = 0 Then
        prime = "NonPrime"
        Exit Function
     End If
    Next k
    prime = "Prime"
End Function

 

[XL_2K3]

Try:

Function prime(num)
    Dim j, k, remain As Integer
    j = Int(num)
    If j <= 1 Then
        prime = "X"
        Exit Function
    End If
    If j <> num Then
      prime = "NonInt"
      Exit Function
    End If
    j = Sqr(num)
    For k = 2 To j
     remain = num Mod k
     If remain = 0 Then
        prime = "NonPrime"
        Exit Function
     End If
    Next k
    prime = "Prime"
End Function

Hey A.P. thanks for the adaptation of the code!

I already had tried adding that one more If statement for j<=1 then "X", but somehow it won't work; instead of retrive the X or the words Prime and Nonprime it retrieves a #NAME? error... Anyhow i already fixed it implementing and IF statement but within the Exel formula and it works great!

Cheers!

 

[Andrew Poulsom]

Hey A.P. thanks for the adaptation of the code!

I already had tried adding that one more If statement for j<=1 then "X", but somehow it won't work; instead of retrive the X or the words Prime and Nonprime it retrieves a #NAME? error... Anyhow i already fixed it implementing and IF statement but within the Exel formula and it works great!

Cheers!

The function worked when I tried it. It certainly didn't give me a #NAME? error.

 

[XL_2K3]

Yuu are absolutely right Andrew, i just checked again using your adapted code and it works fine. At the begining when i copied and paste the formula through a range of cells the first 200 numbers was marked with an "X" but then they got the right output, some times i have a lot of windows and programs opened at the same time so that consume a lot of CPU and memory resource and makes Excel calculations slow, i think that was that made excel retrieve the error message...

Anyway, it's good all works ok now. Thanks again for your help.

 

[tusharm]

You already have a solution to your specific problem but a few pointers...

j is not declared as an integer. It is a variant.

You can skip the test for num being an integer by declaring it as such.

You should test that num is positive but I guess it is covered by the new <=1 test.

You can skip 1/2 of the loops. Outside the loop test for Num Mod 2 =0. If not, then the loop should be from 3 to sqr(num) step 2. This is because once you've tested for divisibility by 2 you can safely skip testing all multiples of 2.

Hi there:

Do you think any of you could help me to make this UDF so when a 0 (zero) or 1 (one) is inputted it retrieves an "X" or any other character as the output? (since 0 and 1 are not prime nor composite numbers).

Well here is the UDF i want to be adapted.

Function prime(num) 
    Dim j, k, remain As Integer 
    j = Int(num) 
    If j <> num Then 
      prime = "NonInt" 
      Exit Function 
    End If 
    j = Sqr(num) 
    For k = 2 To j 
     remain = num Mod k 
     If remain = 0 Then 
        prime = "NonPrime" 
        Exit Function 
     End If 
    Next k 
    prime = "Prime" 
End Function

Thanks a lot in advance!

 

[XL_2K3]

You already have a solution to your specific problem but a few pointers...

j is not declared as an integer. It is a variant.

You can skip the test for num being an integer by declaring it as such.

You should test that num is positive but I guess it is covered by the new <=1 test.

You can skip 1/2 of the loops. Outside the loop test for Num Mod 2 =0. If not, then the loop should be from 3 to sqr(num) step 2. This is because once you've tested for divisibility by 2 you can safely skip testing all multiples of 2.

Hi @tusharm:

I understan what you mean above and i agree. I tried to modify the code as you suggested but, as i'm less than a beginner with VBA, i probably just did the mess with the code!, lol. which certainly i did!

This are the change i did to the original code:

Function prime(num)
    Dim j, k, remain As Integer
    j = Int(num)
    If j <= 1 Then
        prime = "X"
        Exit Function
    End If
      Exit Function
    j = Sqr(num)
    For k = 3 To j
     remain = num Mod k
     If remain = 0 Then
        prime = "NonPrime"
        Exit Function
     End If
    Next k
    prime = "Prime"
End Function

So now i only get 0 (zeros) in the output! .

But i know you know how to do it right!

 

[tusharm]

A great resource to use is the google archive of the Excel newsgroups (groups.google.com). I searched it on your behalf and found a discussion that contained a function by Vasant Nanavati who got it right after a few attempts, a suggested improvement by Jerry Lewis that showed how to speed up the function by skipping checks for multiples of 2, 3, and even 5, and a non-VBA solution by Harlan Grove. See http://groups.google.com/group/micr...functions/browse_frm/thread/919fa5d2078e5045/

In another discussion at http://groups.google.com/group/micr...mming/tree/browse_frm/thread/4b8b96cfaa612430 Frank Kabel points to a page dedicated to prime numbers and a list of the first 1000 prime numbers, which I presume one could load into a VBA collection and then test primality (is there such a word?) with one check!

 

[XL_2K3]

Ok, thanks for the links, i'll have a look...

For now the issue i had with the prime numbers is fixed, (it was a cookie to crack), but i'll be back later with a real challange for any of the Excel MVP and high advanced users who want accept the challenge . As a hint the problem i'm dealing with have to do with the Vlookup and/or match/index functions, in other words i need to invoice/retrieve values from a main table to several secondary tables located in other spreadshhets and workbooks...

Alright, so for now is all and thanks one more time to A.P. and T.M. for your help.

Regards.