# nth Composite Number UDF

#### Juggler_IN

##### Board Regular
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.

### Excel Facts

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

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

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