ISPRIME Lambda Function

Prime Numbers (and AI)

Using ChatGPT and other AI tools to write Excel formulas has become popular. Bill Jelen aka Mr Excel has a simple test to see whether the particular AI engine is reliable: ask for a formula to determine whether a number is a prime number. Until recently, the response is this formula to test cell A2:

=ISPRIME(A2)

This makes use of the well-known but nonexistent ISPRIME function (which does not exist in Google Sheets either). ChatGPT is now using a newer body of knowledge to generate its answers, but it still “may occasionally generate incorrect information”. Bing has a new chat search feature that also uses an updated data source; as of this writing, Google is rushing to catch up.

According to Wikipedia, a prime number is a natural number (a non-negative integer) greater than 1 that has as its natural number factors only itself and 1. Five is a prime number because its only factors are 5 and 1, i.e., 5 = 5 x 1. Six is not a prime number because it can be factored into other natural numbers, i.e., 6 = 6 x 1 = 3 x 2. To test whether a number is prime, we must divide it by all numbers greater than 1 but less than itself; if the remainder of any of these divisions is zero, the number is not prime. In fact, it is only necessary to divide by numbers less than the square root of the number being tested, because any factor greater than the square root is multiplied by a factor less than the square root.

As I mentioned, ChatGPT and Bing have improved their prime number formulas. Bing proposed two formulas, one of which was correct, and after several tries, ChatGPT’s fifth suggestion also was correct. I won’t clutter the internet with the incorrect responses, in case this article becomes part of the universe of knowledge about prime numbers and they are misunderstood to be valid formulas.

I also used a regular web search and found candidate formulas on various websites. Like the AI searches, these results were not necessarily correct. I should note that neither regular nor AI-assisted searches led to an Excel Lambda function that produced correct results.

I modified the two correct formulas and produced these:

=IFS(A2<2,FALSE,A2=2,TRUE,A2<>INT(A2),FALSE,A2>2,AND(MOD(A2,ROW(INDIRECT("2:"&ROUND(SQRT(A2),0))))<>0))

=IFS(A2<2,FALSE,A2=2,TRUE,A2<>INT(A2),FALSE,A2>2,MIN(MOD(A2,ROW(INDIRECT("2:"&ROUND(SQRT(A2),0)))))>0)

These are array formulas, and in older versions of Excel must be entered with Ctrl+Shift+Enter. In Excel 365, a simple Enter will suffice.

ROW(INDIRECT("2:"&ROUND(SQRT(A2),0))) produces a list of row numbers between 2 and the square root of the number in A2, a clever construction that predates the modern SEQUENCE function. MOD performs the division and tells us the remainder. AND tests whether all remainders are not equal to zero (i.e., it’s prime); alternatively, MIN finds the smallest remainder, which is compared to zero (in which case it’s not prime). The first couple of IFS terms take care of 2, which is prime, numbers less than 2, which are not prime, and non-integers, which are not prime.

I don’t know which variation is better, AND(MOD()) or MIN(MOD()). Both produce the correct results, and one is a character longer than the other. But later I’ll describe why I selected the MIN approach for my custom ISPRIME function.

Building a LAMBDA ISPRIME formula

I find that some formulas are easier to build from the inside out. I’ll illustrate my approach here for both variations.

Our first step is to build an array of potential factors. I entered numbers from 0 to 200 in A2:A202, then entered the following formula into B2 and copied it down to B202:

=LAMBDA(n,SEQUENCE(1,SQRT(n),2))(A2)

The top part of the worksheet looks like this. Each value in column A has a horizontal array of factors. B2 contains a #CALC! error, because it’s asking for an array (sequence) of zero values. Rows 3:5 have one-element arrays containing the factor 2, but larger values in column A produce larger arrays with factors 2, 3, 4, etc.

The next step is to divide the input number by the factors and check the remainders. The formula is

=LAMBDA(n,MOD(n,SEQUENCE(1,SQRT(n),2)))(A2)

and the worksheet now looks like this. The array of factors in each row has been converted into an array of remainders.

The “AND” Version

To use the AND approach, we must first compare the remainders to zero. Our working Lambda becomes

=LAMBDA(n,MOD(n,SEQUENCE(1,SQRT(n),2))>0)(A2)

and the worksheet now looks like this. Remainders equal to zero become FALSE and those greater than zero become TRUE.

Now we can use AND to test whether all Boolean values in an array are TRUE, indicating a prime number. Our formula evolves to

=LAMBDA(n,AND(MOD(n,SEQUENCE(1,SQRT(n),2))>0))(A2)

and the worksheet changes to this. Note that the array in each row has been distilled into a single value.

The above correctly tells us which values are prime (TRUE), except for 0, 1, and 2, and any non-integer values. We update the Lambda to include these cases:

=LAMBDA(n,IFS(n<2,FALSE,n=2,TRUE,n<>INT(n),FALSE,n>2,AND(MOD(n,SEQUENCE(1,SQRT(n),2))<>0)))(A2)

Finally we have our ISPRIME Lambda function, AND version.

The “MIN” Version

First we identify our minimum remainder using MIN in the formula

=LAMBDA(n,MIN(MOD(n,SEQUENCE(1,SQRT(n),2))))(A2)

This transforms the array in each row into a single value.

Now we test whether the minimum remainder is greater than zero, indicating a prime number, using this formula.

=LAMBDA(n,MIN(MOD(n,SEQUENCE(1,SQRT(n),2)))>0)(A2)

This converts the column of zero and one values to TRUE and FALSE values.

This correctly shows which values are prime (TRUE), except for 0, 1, and 2, and any non-integer values. We modify our Lambda to include these cases:

=LAMBDA(n,IFS(n<2,FALSE,n=2,TRUE,n<>INT(n),FALSE,n>2,MIN(MOD(n,SEQUENCE(1,SQRT(n),2)))>0))(A2)

Now we have our ISPRIME Lambda function, MIN version.

Creating our Lambda ISPRIME Custom Function

Our Lambda formulas so far have this syntax:

=LAMBDA(input,calculation)(reference)

We turn our Lambda into a custom function by defining a Name, and using the formula above without the reference:

=LAMBDA(n,IFS(n<2,FALSE,n=2,TRUE,n<>INT(n),FALSE,n>2,MIN(MOD(n,SEQUENCE(1,SQRT(n),2)))>0))

Go to Formulas > Define Name to open the New Name dialog. Enter “ISPRIME” in Name, “Input a number to determine whther it is prime” in Comment, and the Lambda formula in Refers To.

Click OK to create the function. We can open the Name Manager (Ctrl+F3) to see this and any other custom functions in the workbook.

I stated earlier that I didn’t know which version of the ISPRIME formula was better, the one using AND or the one using MIN. I selected the MIN version as my custom function because it converted the intermediate calculation to a single value one step earlier than the AND version. A more rigorous approach would enter each function into many cells and measure how long Excel takes to recalculate each one.

Using our ISPRIME Function

Click in a cell where you want the new ISPRIME function and start typing ISPRIME. Before you even finish, IntelliSense will find the function and show the description in a tooltip.

Click Tab, and Excel inserts the full name and opening parenthesis. Click on the cell with a potential prime number that you want to test, and click Enter. Here is the sheet with the new function filled down:

More Articles About Dynamic Arrays, LET, and LAMBDA

• Lambda Moving Average Formulas
• Improved Excel Lambda Moving Average
• Excel Lambda Moving Average
• LAMBDA Function to Build Three-Tier Year-Quarter-Month Category Axis Labels
• Dynamic Array Histogram
• Calculate Nice Axis Scales with LET and LAMBDA
• VBA Test for New Excel Functions
• Dynamic Arrays, XLOOKUP, LET – New Excel Features