nth Composite Number UDF

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
218
Platform
Windows
I am looking for a UDF to compute the nth composite number (nth non-prime number) similar to the nth prime.

for e.g.,
1st prime number is 2. 1st composite is 4.
10th prime number is 29. 10th composite is 18.
100th prime number is 541. 100th composite is 133.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,074
Here's a UDF that calculates either:

Code:
Public Primality As String


Public Function NumType(ByVal ntype As String, ByVal loc As Long)
Dim i As Long, j As Long, a As Long, n As Long, x As Long


    If Len(Primality) = 0 Then Primality = "XP"
    
    ntype = UCase(ntype)
    If ntype <> "P" And ntype <> "C" Then
        NumType = "Invalid code"
        Exit Function
    End If
    
ChkAgain:
    a = 0
    For i = 2 To Len(Primality)
        If Mid(Primality, i, 1) = ntype Then
            a = a + 1
        End If
        If a = loc Then
            NumType = i
            Exit Function
        End If
    Next i
        
    n = Len(Primality) + 1
    If n > 1000000 Then
        NumType = "The requested value is over 1000000"
        Exit Function
    End If
    
    Primality = Primality & String(1000, "P")
    For i = 2 To Len(Primality)
        If Mid(Primality, i, 1) = "P" Then
            x = Int(n / i) * i
            x = IIf(x = 0, i, x)
            x = IIf(x <= i, i * 2, x)
            For j = x To Len(Primality) Step i
                Mid(Primality, j, 1) = "C"
            Next j
        End If
    Next i
    GoTo ChkAgain:
    
End Function
You call it like so:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">11</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">13</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">17</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">19</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">23</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">29</td><td style="text-align: right;;">18</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=numtype(<font color="Blue">"P",ROWS(<font color="Red">$B$2:$B2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=numtype(<font color="Blue">"C",ROWS(<font color="Red">$C$2:$C2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

It uses memoization to store previously computed results so it doesn't have to recompute every time. I put an upper limit of 1000000 on it, but you can change that if you like. It uses a version of the Sieve of Eratosthenes to calculate the primes. If the next requested function exceeds the number of primes/composites already calculated, it adds another 1000 numbers and tries again (up to 1000000).

Let us know if this works for you.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,074
A few tweaks to it to improve performance:

Code:
Public Primality As String

Public Function NumType(ByVal ntype As String, ByVal loc As Long)
Dim i As Long, j As Long, n As Long, x As Long, p2 As String

    If Primality = "" Then Primality = "XP"
    
    ntype = UCase(ntype)
    If ntype <> "P" And ntype <> "C" Then
        NumType = "Invalid code"
        Exit Function
    End If
    
ChkAgain:
    p2 = WorksheetFunction.Substitute(Primality, ntype, "~", loc)
    x = InStr(p2, "~")
    If x > 0 Then
        NumType = x
        Exit Function
    End If
        
    n = Len(Primality) + 1
    If n > 1000000 Then
        NumType = "The requested value is over 1000000"
        Exit Function
    End If
    
    Primality = Primality & String(1000, "P")
    For i = 2 To Len(Primality)
        If Mid(Primality, i, 1) = "P" Then
            x = Int(n / i) * i
            x = IIf(x <= i, i * 2, x)
            For j = x To Len(Primality) Step i
                Mid(Primality, j, 1) = "C"
            Next j
        End If
    Next i
    GoTo ChkAgain:
    
End Function
 
Last edited:

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
218
Platform
Windows
This meets the first part as it does output both primes and composites. What i also need is the nth value. Say if I input n=4, then it should output 4th prime = 7 and 4th composite = 9.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,074
It does that. Enter:

=NumType("P",4)

and it will return 7.

=NumType("C",4)

returns 9. Perhaps the fact that I used ROWS() to generate 1,2,3,4, etc. was confusing.
 

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
218
Platform
Windows
@Eric W;

This does give the output, but is failing at a higher LOC value. It gives output for C at 10000 but is failing for P at 10000. It starts to fail for C at 30000.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,074
The problem is that the Worksheet function Substitute, which I used in the macro, only allows up to 32767 characters. I replaced it with the VBA Replace function, which is too bad, since it's slower. But here's the updated version. I changed the lines in red:

Rich (BB code):
Public Primality As String

Public Function NumType(ByVal ntype As String, ByVal loc As Long)
Dim i As Long, j As Long, n As Long, x As Long, p2 As String

    If Primality = "" Then Primality = "XP"

    ntype = UCase(ntype)
    If ntype <> "P" And ntype <> "C" Then
        NumType = "Invalid code"
        Exit Function
    End If
    
ChkAgain:
    p2 = Replace(Primality, ntype, "~", , loc - 1)
    x = InStr(p2, ntype)
    If x > 0 Then
        NumType = x
        Exit Function
    End If
        
    n = Len(Primality) + 1
    If n > 1000000 Then
        NumType = "The requested value is over 1000000"
        Exit Function
    End If
    
    Primality = Primality & String(10000, "P")
    For i = 2 To Len(Primality)
        If Mid(Primality, i, 1) = "P" Then
            x = Int(n / i) * i
            x = IIf(x <= i, i * 2, x)
            For j = x To Len(Primality) Step i
                Mid(Primality, j, 1) = "C"
            Next j
        End If
    Next i
    GoTo ChkAgain:

End Function
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,041
Messages
5,508,953
Members
408,702
Latest member
daz457

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top