Suppose you are asked to find out the number of prime numbers in an interval in MS Excel. A prime number is a number that cannot be evenly divided by any other number except 1 and itself. For example, some prime numbers are 2, 3, 5, 7, 11, 13, 17, and so on. These numbers have no divisors other than 1 and the number itself.

Suppose lower limit of a range is entered in cell C2 and upper limit in cell C3.
Solution
- Press CTRL + F3 to open the Name Manager dialog box.
- Click on the "New" button.
- In the "Name" field, type rng.
- In the "Refers to:" box, enter =ROW(INDIRECT(Sheet1!$C$2&":"&Sheet1!$C$3))
- Click OK
- Click Close

In cell E5, enter the following formula :
=SUM(IF(MMULT(--(IF(rng>TRANSPOSE(rng-C2+2),MOD(rng,(rng>TRANSPOSE(rng-C2+2))*TRANSPOSE(rng-C2+2)))=0),rng-C2+2)=0,1))
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 :
Generating prime numbers in an interval with Excel
Check if a number is prime with Excel
Great stuff! Thanks for posting... I love to see how things work... Also, I like to see how far you can push Excel before it breaks. I expanded the list of primes from 25 to 500, runs slow but still works.
ReplyDelete