How to generate prime numbers with Excel

Deepanshu Bhalla 1 Comment
Scenario

Suppose you are asked to find out all the prime numbers in an interval using MS Excel.

Generate prime numbers with Excel
Suppose lower limit of a range is entered in cell C2 and upper limit in cell C3.
Steps to generate prime numbers with Excel
  1. Press CTRL + F3 to open the Name Manager.
  2. Click on "New".
  3. In the "Name" field, type rng
  4. In the "Refers to:" box, enter =ROW(INDIRECT(Sheet1!$C$2&":"&Sheet1!$C$3))
    Refers to: Name Manager
  5. Click OK
  6. Click "New"
  7. In the "Name" field, type Prime
  8. In the "Refers to:" box, enter:
    =SMALL(IF(MMULT(--(IF(rng>TRANSPOSE(rng-Sheet1!$C$2+2),MOD(rng,(rng>TRANSPOSE(rng-Sheet1!$C$2+2))*TRANSPOSE(rng-Sheet1!$C$2+2)))=0),rng-Sheet1!$C$2+2)=0,rng),ROW(INDIRECT("1:"&Sheet1!$C$3)))
    

    Array Formula: Prime Number
  9. Click OK
  10. Click Close

Excel 2007 or later versions :
Select cells C5:C25, enter =IFERROR(Prime,"")

Excel 2003 or earlier versions :
Select cells C5:C25, enter =IF(ISERROR(Prime),"",Prime)

Hit CTRL + SHIFT + ENTER to confirm this formula as an array formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

Download the workbook

Related Post : 
Count prime numbers with Excel
Check if a number is prime with Excel
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 1 Response to "How to generate prime numbers with Excel"
  1. This doesn't seem to work - I'm using Excel 2016. Only the first prime in the interval is generated. Any ideas?

    ReplyDelete
Next → ← Prev