Scenario
Suppose you are asked to find out all the prime numbers in an interval.
Suppose lower limit of a range is entered in cell C2 and upper limit in cell C3.
Solution
1. Press CTRL + F3 to open name manager
2. Click "New"
3. In "Name", type rng
4. In "Refers to: " box, enter =ROW(INDIRECT(Sheet1!$C$2&":"&Sheet1!$C$3))
5. Click OK
6. Click "New"
7. In "Name", type PRIME
8. In "Refers to: " box, enter
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 :
Counting prime numbers in an interval with Excel
Check if a number is prime with Excel
Suppose you are asked to find out all the prime numbers in an interval.
Suppose lower limit of a range is entered in cell C2 and upper limit in cell C3.
Solution
1. Press CTRL + F3 to open name manager
2. Click "New"
3. In "Name", type rng
4. In "Refers to: " box, enter =ROW(INDIRECT(Sheet1!$C$2&":"&Sheet1!$C$3))
5. Click OK
6. Click "New"
7. In "Name", type PRIME
8. In "Refers to: " box, enter
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 :
Counting prime numbers in an interval with Excel
Check if a number is prime with Excel
This doesn't seem to work - I'm using Excel 2016. Only the first prime in the interval is generated. Any ideas?
ReplyDelete