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
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.
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