Using Excel Dynamic Arrays to generate a list of prime numbers in Excel. Also in this video: Creating a Custom List of numbers. Quickly converting numbers to text using TRIM instead of TEXT.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2309.
Generate prime numbers in Excel. Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen.
I was doing a seminar here in Cape Canaveral and someone said, hey, how can I teach Excel to fill prime numbers? This is one of those classic Excel problems.
It was really hard to do before, but now thanks to dynamic arrays, this is going to be simpler.
Before you proceed in this video, you need to open your Excel and see if you have this SORT function.
If you type =SORT( to see if it it doesn't know what you're talking about.
Then you don't have dynamic arrays.
You have to go to Office 365.
You know, in order to have these formulas, so before I can teach Excel to fill prime numbers, I want to figure out how Excel can generate a list of prime numbers.
I'm going to show you how to do this just using three formulas, but let's walk through it step by step.
So the first thing I want to do is I want to generate a sequence of numbers so =SEQUENCE(.
I'll want to generate from 2 to ten thousand. So 9999 rows.
Number of columns: I'll leave it blank, that'll be one and start at 2 like that.
So, it generates the numbers from 2 to 10,000 and that just gives me all the numbers I want to test.
This will generate all the prime numbers from 2 to 10,000.
If you needed more, just bump that 9999 up. Alright now.
How to tell if a number is prime?
You have to take that number and divide the number by every number from 2 up to the (number minus one).
Is any quotient a whole number without any decimal remainders?
If you get anything where it is evenly divisible, then it's not prime.
You're looking for all of those quotients to have a remainder that is.a non-zero remainder. Now.
Here's the shortcut for this.
Let's say we wanted to test the number 60.
You have to do all the numbers from 2 to 60, but not really because clearly once you get to 30 you're halfway there.
So all of the factors of 60: Two times 30, 3 times 20, 4 times 15, 5 times 12, 6 times 10.
But the rest of the factors are all reverse right? So 2 * 30.
We already checked, you don't have to check 30 * 2 or.
20 * 3 or 15 * 4, 12 * 5 or 10 * 6 because we've already checked it with the small numbers.
So the shortcut.
Is you have to check from 2 to the square root of the number, so in this case for 60, if I would check everything from 2:00 to 7:00 for 10,001 I would have to check from 2 to 100.
That dramatically limits how much we have to check, so I'm going to figure out the square root and I actually want to round that down.
So that's the INTeger function of SQRT of A2 hashtag.
The hashtag says: however many numbers are returned over there in column A...
return that many numbers here and it gives me all the square roots.
Now here I think I'd have just down to 100.
So like in that last case there we have to check in, remember from 2 to 10.
Now I'm going to generate those numbers from 2 to whatever number is over there in column B and this actually make a lot more sense down here at the bottom.
So we're going for =SEQUENCE(. I want 1 row and how many columns?
I want not quite 10. I want 10 - 1.
Where do I want to start I will start at two and the step will automatically be one.
See that generates the numbers two through 10.
As I copy that up, Beautiful. OK, now this works.
So we're going to check everything from 2 to 2. From 2 to 3. From 2 to 4. 2 to 5.
2 to 6 and so on.
Why are we getting a CALC error here is there because I'm saying that I want zero columns in the sequence and that returns a empty array. And currently Excel can't deal with an empty array.
So two and three are prime numbers.
We have to leave them in there and we have to deal with that later, but for right now we're just going to have CALC errors there in those first 2.
And we will handle that as time goes by.
Alright, so here's my SEQUENCE function.
I want to divide that sequence into the number, so 2 into 4 three in to 9, four into 16 and so on.
So right here will say equal A2 divided by. And I double Click to copy that down, yes, alright?
So there's our division and what I'm really interested in is all of this stuff after the decimal point.
So here in Step 5, How do I figure out what the remainder is?
I take the MOD (that is short for modulo), and I want divided by one and see what the remainder is.
So we'll wrap this whole thing in the MOD function, comma 1 at the end, and what we're looking for is where for a series where all of the reminders are non zero, like here at 97.
That's a prime number Alright, but here where we have some even divisions and there was no remainder those numbers are not prime.
So we used the SEQUENCE function here.
INT and SQRT there and then another SEQUENCE function and then the MOD.
And this is returning a series of numbers.
I really just want to figure out what's the smallest MOD so I wrapped that whole thing in MIN like that.
And again what we're interested in is anytime that the smallest remainder was non zero like that's a prime number and 89 prime number and 83 is a prime number. And 79 alright.
So come along come along here just to handle two and three where sequences generating a CALC error.
I know that both of those numbers are the only place where we're gonna have this problem.
They are prime, so if there's an error there, I'm going to put it in non 0 number anything you want. I went with 1.
And this was surprising to me. I was ready to record the podcast.
I said wait a second.
What would happen if instead of column B here, if we embedded that calculation right inside of column C and I said, well, it can't be faster because this is one formula generating 10,000 results.
If I invoke INT and SQRT 10,000 times, it has to be slower.
But I use Charles Williams calc timer and it's actually about 20% faster to take that formula in B and embed it into C.
Completely counterintuitive, When I see Charles in March, I will have to ask him about that and see what he thinks.
So right here we'll take that B2 and I'll paste in the formula.
I don't need the hashtag anymore.
Double clicking, copy that down.
And now we can delete column B.
Alright, even a little bit shorter.
Alright, here we are final step.
So now that I have all of those numbers and what the smallest remainder is, another dynamic array here is filter so I want to filter B2# And what I want I want where B2.
Little annoying there.
I can't do B2# because that's not an array, it's just a bunch of formulas. Uh, yeah.
And if I would have used, I could've used OFFSET and A2#, but then OFFSET makes the whole thing volatile and I just didn't want to go down that path.
What I want is where that is greater than zero.
An bingo, there's my list. Checked it against the Internet here.
SplashLearn.com has a list of the prime numbers: 2, 3, 5, 7, 11, 13.
Yeah it is working alright so recap we started out here with the SEQUENCE function to generate the series of numbers and then in column B a formula that really is doing five things.
Generates the sequence from 2 to the square root of what we're looking for.
Divide that number by the SEQUENCE. Isolate the remainders.
What's the smallest remainder and then eliminate the CALC error for two and three and thanks to the formula, anything that is non-0 is a prime number.
OK, There you are. So now back to Cape Canaveral, FL.
Doing that seminar there and they didn't say "How can I get Excel to generate prime numbers?" They wanted Excel to fill the prime numbers, fill the prime numbers. Oh wow.
OK, so here's what we're going to do.
I'm going to take those numbers in column F and I'm going to convert them to text.
Now normally I would use the TEXT function for this.
The TEXT of F3 and then "0" number format, but I was in Chicago and Rob who is in my seminar.
I said Hey, it faster way to convert numbers to text is just use the TRIM() function.
The TRIM like that.
So I will ask for the TRIM() and I actually do this all in one go.
Here the TRIM of F3 to F256 Generates the numbers from 2 to 1609.
Convert those numbers to values. Control C, alt E S V.
And then I'm going to teach, including the word prime.
That'll allow me to very quickly fill this, so I choose from prime on down.
Go to File, Options.
Advanced.
In Advanced, Scroll all the way down to the bottom, which reveals this button for Edit Custom Lists.
I have selected H2 to H256 that can only be 255 cells.
Click Import and click OK.
Click OK and the next time any workbook (I'll just do CTRL N). I need to fill the prime numbers.
I type the word prime, grab the fill handle and drag and it will generate prime numbers like that.
You can go across or go down and it will work.
Now right now the hassle here is it generating numbers as text.
Very simple, just choose all of the cells Alt+D E F and it will convert back.
While these dynamic arrays are really, really cool, this book Excel Dynamic Arrays Straight to the Point has lots and lots of examples.
Things you can do with dynamic arrays.
Click the "i" in the top right hand corner $3 to buy that book.
If you like these tips please subscribe to this channel and ring that bill so you can be notified when I have new Excel tips and tricks.
Feel free to post any questions or comments in the comment section below.
Post below if you have a better way to do this. this is a complicated video.
I'm sure there are improvements, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
Generate prime numbers in Excel. Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen.
I was doing a seminar here in Cape Canaveral and someone said, hey, how can I teach Excel to fill prime numbers? This is one of those classic Excel problems.
It was really hard to do before, but now thanks to dynamic arrays, this is going to be simpler.
Before you proceed in this video, you need to open your Excel and see if you have this SORT function.
If you type =SORT( to see if it it doesn't know what you're talking about.
Then you don't have dynamic arrays.
You have to go to Office 365.
You know, in order to have these formulas, so before I can teach Excel to fill prime numbers, I want to figure out how Excel can generate a list of prime numbers.
I'm going to show you how to do this just using three formulas, but let's walk through it step by step.
So the first thing I want to do is I want to generate a sequence of numbers so =SEQUENCE(.
I'll want to generate from 2 to ten thousand. So 9999 rows.
Number of columns: I'll leave it blank, that'll be one and start at 2 like that.
So, it generates the numbers from 2 to 10,000 and that just gives me all the numbers I want to test.
This will generate all the prime numbers from 2 to 10,000.
If you needed more, just bump that 9999 up. Alright now.
How to tell if a number is prime?
You have to take that number and divide the number by every number from 2 up to the (number minus one).
Is any quotient a whole number without any decimal remainders?
If you get anything where it is evenly divisible, then it's not prime.
You're looking for all of those quotients to have a remainder that is.a non-zero remainder. Now.
Here's the shortcut for this.
Let's say we wanted to test the number 60.
You have to do all the numbers from 2 to 60, but not really because clearly once you get to 30 you're halfway there.
So all of the factors of 60: Two times 30, 3 times 20, 4 times 15, 5 times 12, 6 times 10.
But the rest of the factors are all reverse right? So 2 * 30.
We already checked, you don't have to check 30 * 2 or.
20 * 3 or 15 * 4, 12 * 5 or 10 * 6 because we've already checked it with the small numbers.
So the shortcut.
Is you have to check from 2 to the square root of the number, so in this case for 60, if I would check everything from 2:00 to 7:00 for 10,001 I would have to check from 2 to 100.
That dramatically limits how much we have to check, so I'm going to figure out the square root and I actually want to round that down.
So that's the INTeger function of SQRT of A2 hashtag.
The hashtag says: however many numbers are returned over there in column A...
return that many numbers here and it gives me all the square roots.
Now here I think I'd have just down to 100.
So like in that last case there we have to check in, remember from 2 to 10.
Now I'm going to generate those numbers from 2 to whatever number is over there in column B and this actually make a lot more sense down here at the bottom.
So we're going for =SEQUENCE(. I want 1 row and how many columns?
I want not quite 10. I want 10 - 1.
Where do I want to start I will start at two and the step will automatically be one.
See that generates the numbers two through 10.
As I copy that up, Beautiful. OK, now this works.
So we're going to check everything from 2 to 2. From 2 to 3. From 2 to 4. 2 to 5.
2 to 6 and so on.
Why are we getting a CALC error here is there because I'm saying that I want zero columns in the sequence and that returns a empty array. And currently Excel can't deal with an empty array.
So two and three are prime numbers.
We have to leave them in there and we have to deal with that later, but for right now we're just going to have CALC errors there in those first 2.
And we will handle that as time goes by.
Alright, so here's my SEQUENCE function.
I want to divide that sequence into the number, so 2 into 4 three in to 9, four into 16 and so on.
So right here will say equal A2 divided by. And I double Click to copy that down, yes, alright?
So there's our division and what I'm really interested in is all of this stuff after the decimal point.
So here in Step 5, How do I figure out what the remainder is?
I take the MOD (that is short for modulo), and I want divided by one and see what the remainder is.
So we'll wrap this whole thing in the MOD function, comma 1 at the end, and what we're looking for is where for a series where all of the reminders are non zero, like here at 97.
That's a prime number Alright, but here where we have some even divisions and there was no remainder those numbers are not prime.
So we used the SEQUENCE function here.
INT and SQRT there and then another SEQUENCE function and then the MOD.
And this is returning a series of numbers.
I really just want to figure out what's the smallest MOD so I wrapped that whole thing in MIN like that.
And again what we're interested in is anytime that the smallest remainder was non zero like that's a prime number and 89 prime number and 83 is a prime number. And 79 alright.
So come along come along here just to handle two and three where sequences generating a CALC error.
I know that both of those numbers are the only place where we're gonna have this problem.
They are prime, so if there's an error there, I'm going to put it in non 0 number anything you want. I went with 1.
And this was surprising to me. I was ready to record the podcast.
I said wait a second.
What would happen if instead of column B here, if we embedded that calculation right inside of column C and I said, well, it can't be faster because this is one formula generating 10,000 results.
If I invoke INT and SQRT 10,000 times, it has to be slower.
But I use Charles Williams calc timer and it's actually about 20% faster to take that formula in B and embed it into C.
Completely counterintuitive, When I see Charles in March, I will have to ask him about that and see what he thinks.
So right here we'll take that B2 and I'll paste in the formula.
I don't need the hashtag anymore.
Double clicking, copy that down.
And now we can delete column B.
Alright, even a little bit shorter.
Alright, here we are final step.
So now that I have all of those numbers and what the smallest remainder is, another dynamic array here is filter so I want to filter B2# And what I want I want where B2.
Little annoying there.
I can't do B2# because that's not an array, it's just a bunch of formulas. Uh, yeah.
And if I would have used, I could've used OFFSET and A2#, but then OFFSET makes the whole thing volatile and I just didn't want to go down that path.
What I want is where that is greater than zero.
An bingo, there's my list. Checked it against the Internet here.
SplashLearn.com has a list of the prime numbers: 2, 3, 5, 7, 11, 13.
Yeah it is working alright so recap we started out here with the SEQUENCE function to generate the series of numbers and then in column B a formula that really is doing five things.
Generates the sequence from 2 to the square root of what we're looking for.
Divide that number by the SEQUENCE. Isolate the remainders.
What's the smallest remainder and then eliminate the CALC error for two and three and thanks to the formula, anything that is non-0 is a prime number.
OK, There you are. So now back to Cape Canaveral, FL.
Doing that seminar there and they didn't say "How can I get Excel to generate prime numbers?" They wanted Excel to fill the prime numbers, fill the prime numbers. Oh wow.
OK, so here's what we're going to do.
I'm going to take those numbers in column F and I'm going to convert them to text.
Now normally I would use the TEXT function for this.
The TEXT of F3 and then "0" number format, but I was in Chicago and Rob who is in my seminar.
I said Hey, it faster way to convert numbers to text is just use the TRIM() function.
The TRIM like that.
So I will ask for the TRIM() and I actually do this all in one go.
Here the TRIM of F3 to F256 Generates the numbers from 2 to 1609.
Convert those numbers to values. Control C, alt E S V.
And then I'm going to teach, including the word prime.
That'll allow me to very quickly fill this, so I choose from prime on down.
Go to File, Options.
Advanced.
In Advanced, Scroll all the way down to the bottom, which reveals this button for Edit Custom Lists.
I have selected H2 to H256 that can only be 255 cells.
Click Import and click OK.
Click OK and the next time any workbook (I'll just do CTRL N). I need to fill the prime numbers.
I type the word prime, grab the fill handle and drag and it will generate prime numbers like that.
You can go across or go down and it will work.
Now right now the hassle here is it generating numbers as text.
Very simple, just choose all of the cells Alt+D E F and it will convert back.
While these dynamic arrays are really, really cool, this book Excel Dynamic Arrays Straight to the Point has lots and lots of examples.
Things you can do with dynamic arrays.
Click the "i" in the top right hand corner $3 to buy that book.
If you like these tips please subscribe to this channel and ring that bill so you can be notified when I have new Excel tips and tricks.
Feel free to post any questions or comments in the comment section below.
Post below if you have a better way to do this. this is a complicated video.
I'm sure there are improvements, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
