A prime number (or a prime) is a natural number greater than 1 that has no positive divisors other than 1 and itself.
Divide a number by each number between 2 and square root of the number. If the number has no factors less than its square root, then n is prime.
Let's take a number 53
- Calculate square root of 53 i.e. 7.28
- Round 7.28 up i.e. 8
- Divide 53 by each number between 2 and 8 (2,3,4,5,6,7,8)
- You'll see there is always a remainder when 53 is divided by each number 2 and 8
- Result : Prime
Excel Formula to check prime number
Suppose a value is entered in cell B4 . Paste the below formula in cell C4.
=IF(B4=2,"Prime",IF(AND(MOD(B4,ROW(OFFSET($A$2,,,ROUNDUP(SQRT(B4),0)-1)))<>0),"Prime","Not 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 workbookCounting Prime Numbers in an Interval with Excel
Generating Prime Numbers in an Interval 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.
Not working for 9. Problem with some indirect addressing.
ReplyDeleteIt is working fine for 9. Download the workbook (link provided in the article)
DeleteYour formula references A2, but doesn't say what I should be putting there. It's great that your formula works for your specific workbook, but if it can't be easily copied into another, then what is the point of publishing it.
DeleteAnonymous, it is very easily copied into any workbook, and it works exactly as advertised. The A2 reference makes no difference - a little investigation would have shown you that. And on that note, even if the formula wasn't directly translatable, would it really hurt to have to -gasp!- do a little of your own homework? There's a lot to learn from this post - that would be "the point" of publishing it.
Deletetry 1681 it says its prim but 41^2 = 1681
Deleteit says not prime for 1681 in my case
DeleteThe reason it is not working for some people is, I think, they are skipping the Ctrl Shift Enter aspect to make it an array formula.
DeleteDoesn't seem to be 100% accurate, for example, For the input I entered 111,111,111. It said it was prime, although it can be divided by 9 and 3 at least. Any idea what may cause this kind of behavior? I don't really understand how the function works, so I can't diagnose the problem myself.
ReplyDeleteIt says 'Not Prime' for 111,111,111. You can save your workbook to any cloud drive and share link with me to debug.
DeleteFrom Malinborn asnswer: The reason it is not working for some people is, I think, they are skipping the Ctrl+Shift+Enter aspect to make it an array formula.
DeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
DeleteThis comment has been removed by the author.
DeleteThis comment has been removed by the author.
DeleteClever!! I never would have thought of using an array formula iterating along a virtual array to create a "for loop" inside an excel formula! I'm definitely going to have to remember that technique in the future!
ReplyDeleteThough, as evidenced by the comments, that technique is extremely advanced (and thus confusing) for most people. Perhaps a link to a post with an explanation of the technique would help.
Careful using this with numbers 81 and larger...
ReplyDeleteIt works fine for me. Make sure to press CTRL + SHIFT + ENTER after entering the formula but BEFORE clicking away. You will know that you entered it correctly if selecting the cell shows
Delete{=IF(B4=2,"Prime",IF(AND(MOD(B4,ROW(OFFSET($A$2,,,ROUNDUP(SQRT(B4),0)-1)))<>0),"Prime","Not Prime"))}
note the {} brackets.
Also note that entering the brackets manually does NOT work; you have to press CTRL + SHIFT + ENTER to get the brackets.
Shows 6 as a prime, not correct
ReplyDeleteThe formula works, the most common mistake is not making it an array formula by pressing Ctrl+Shift+Enter. If you don't make it an array formula it will return false positives (like 9, 81, and 1681).
DeleteThank you that is genius, works fine for me, shows 6 and 9 as prime.
ReplyDelete74 Prime
ReplyDelete75 Not Prime
76 Not Prime
77 Not Prime
78 Not Prime
79 Prime
80 Not Prime
81 Not Prime
82 Prime
83 Prime
84 Not Prime
85 Not Prime
86 Prime
87 Not Prime
88 Not Prime
89 Prime
90 Not Prime
91 Not Prime
92 Not Prime
93 Not Prime
94 Prime
Thanks, this formula works completely fine with me. I would really appreciate, if you could give a little bit explanation for this particular part - IF(AND(MOD(B4,ROW(OFFSET($A$2,,,ROUNDUP(SQRT(B4),0)-1)))<>0),"Prime","Not Prime"))
ReplyDeleteThe formula creates a virtual array from 2 (which is why A2 is used in the formula) to the square root of the number (because there are no possible multiples larger than the square root of a given number). That is the "OFFSET($A$2,,,ROUNDUP(SQRT(B4),0)-1)" section.
DeleteNext, every function the array is passed to acts on every entry in that array until it is summarized by the AND function.
So first the array is passed to the ROW function which places the "row number" in each array entry. The array of numbers is then passed to the MOD function as the divisor. The formula then returns the remainder when the number in question is divided by the number in that entry in the array. That is the "MOD(B4,...)" section. It then tests each entry to see if that result is equal to 0, if an entry in the array is 0, then it returns FALSE. The array is now a list of TRUE and FALSE values that are then summarized with the AND function. AND takes the array of Boolean values and returns a single Boolean value.
So, if the number is not prime then there is a divisor somewhere in the array of integers and the MOD function returns 0, the logical comparator "<>0" then returns FALSE which then makes AND false, causing the IF function to return "Not Prime".
Because the formula uses an array, you MUST tell Excel to process it as an Array Formula by entering it with Ctrl+Shift+Enter. Otherwise, if you don't make it an array formula it will return false positives (like 9, 81, 1681, and now the latest entry, 21).
This Formula is not correct because for 21 it also says it is a prime number but 21 can be divided by 3 and so this formula is not perfect i want a perfect formula brother
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThe correct formula should be like this:
ReplyDelete=IF($C$4=2,"Prime",IF(AND(MOD(C4,ROW(OFFSET($A$2,,,ROUNDUP(SQRT($C$4),0)-1)))<>0,MOD($C$4,SQRT($C$4))<>0),"Prime","Not Prime"))
Why test for the square root again? It's already included in the virtual array.
ReplyDeleteWhen evaluated as an array formula, the offset function creates an array from 2 to the square root (rounded up, so the square root is always included). Your function just adds a test for the un-rounded square root. But, the only time an un-rounded square root would make a difference (ie, be a divisor) is if it happens to be an integer, which is already included in the array as the round functions don't change an integer.
As long as you make it an array formula with Ctrl+Shift+Enter, this modification won't change anything.
Very clever. the AND function with a single Logical parameter confused me until I saw the that the array was being summarized via the CSE capability. Wow a real brain twister.
ReplyDelete