Prime Numbers

XL_2K3

New Member
Joined
Aug 16, 2007
Messages
39
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.

Code:
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! :biggrin:
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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

New Member
Joined
Aug 16, 2007
Messages
39
Try:

Code:
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! (y)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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! (y)

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

XL_2K3

New Member
Joined
Aug 16, 2007
Messages
39

ADVERTISEMENT

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.

Code:
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! :biggrin:
 

XL_2K3

New Member
Joined
Aug 16, 2007
Messages
39

ADVERTISEMENT

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! :oops:

This are the change i did to the original code:

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! :eek: .

But i know you know how to do it right! :LOL:
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

New Member
Joined
Aug 16, 2007
Messages
39
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 :LOL:. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,734
Members
414,170
Latest member
Mdm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top