tag:blogger.com,1999:blog-7958828565254404797.post2631365890669947992..comments2024-03-29T00:02:14.892-07:00Comments on ListenData: Check if a number is prime with ExcelDeepanshu Bhallahttp://www.blogger.com/profile/09802839558125192674noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-7958828565254404797.post-46352650569841424422024-03-23T07:22:24.531-07:002024-03-23T07:22:24.531-07:00Thanks for the detailed explanation. I never thoug...Thanks for the detailed explanation. I never thought users would intentionally or unintentionally check prime numbers for decimal numbers. Thanks for bringing it to my attention. Cheers!Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-49354487784546097012024-03-23T04:59:02.807-07:002024-03-23T04:59:02.807-07:00Some notes:
N1. The formulas presented here are in...Some notes:<br />N1. The formulas presented here are in 'international locale format' - most notably the semi-colon separator (;) because it better serves the understanding of readers who use international versions of Excel. If you use an English version of Excel (or an English "regional settings"), please replace semi-colon (;) per comma (,);<br /><br />N2. I consider Excel formulas a Programming Language, and therefore, they shall be treated in a structured way to make them more readable and easier to understand even because the formulas are not mine alone - hence the spaces that do not affect the formula. IF indentation, Consolas (monospace fixed-width font) in the formula field (fx), when very large, break them with Alt+Enter, etc. If you consider this unnecessary, feel free to delete the spaces in the proposed formulas;<br /><br />N3. These formulas (original and alternative) are suitable for numbers in INTEGER format. Avoid exceeding the Integer limit for your version of Excel - in versions 2019 or 365, the limit is 15 digits "9" (999 999 999 999 999) - in some old versions, it was a power of 2 minus 1;<br /><br />N4. In versions 2019 or 365, the dynamic array is automatic - no need for {CSE} (Ctrl+Shift+Enter) for both the original formula and the alternatives. If you intend to distribute your spreadsheet to readers with older versions, use {CSE} for compatibility. In any case, it is recommended to keep the MOD(...) calculation column, in this case, the A$ column free to avoid #VALUE or #SPILL errors;<br /><br />N5. The MOD function gives, as a result, the remainder of the division, not the quotient. As far as I remember, this function was always... complicated - I can remember to avoid its use by working around it with formulas such as "A1/B1 - INT(A1/B1) <> 0". The XLS-type versions had a MOD limited to 255 or 65535 in the divider; in other XLSX-type versions, there were precision errors if the number was greater than 256M and divisor 2. Only in recent updates to version 365 did the MOD function become more flexible. Please check the MOD function status of your Excel version on the Microsoft Learn Center;<br /><br />N6. Hint: Dynamic arrays are huge resource hogs. If there is a large amount of numbers to test - list the numbers horizontally using the maximum number of cells in the row (recommended). And the primality test formula in the next row. This is because Excel has Z-scanning. Otherwise, by listing the numbers in a single column, Excel checks each cell for values or formulas for all rows up to the last cell in the worksheet, even if they have no content. This procedure is valid for any spreadsheet with lots of data. Always remember to leave the A$ calculation column free: the XLSX-type versions with 1M rows can test integers of up to 12 digits, and the XLS-type versions with 65535 rows can test integers of up to 4G (10 digits). There is not much advantage in changing the ROW function to COLUMN in the provided formulas since the versions have 16K and 255 columns, respectively - these are restricted limits for testing large integers.<br /><br />Hope this helps. A happy prime number hunt for everyone.Axelhttps://www.blogger.com/profile/16057691062779352412noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-58347925323417377152024-03-23T04:58:38.230-07:002024-03-23T04:58:38.230-07:00The formula provided by Deepanshu is a good idea, ...The formula provided by Deepanshu is a good idea, very well engineered - it works AS LONG AS the "integer" to be tested is (actually) an integer - without the inaccuracies of Excel, the IEEE-754 standard, or an old Intel processor that also had this type of problem.<br />Excel uses a modified version of the IEEE-754 standard, where some items do not fully adhere to this standard: Excel disregards, without rounding, digits beyond the 15th position, and as if that were not enough, results from floating-point arithmetic are presented in a cell in a "user-friendly manner" (without precision errors) differently how it is stored in memory in binary form according to IEEE-754. More, decimals of (mainly) multiples of 3 (1/3, 1/6, etc.) generate considerable errors in any binary arithmetic, regardless of the chosen standard.<br />Let's take the example of Muhammad Hassan (April 27, 2020):<br />1. If we type "21" in the testing cell: [ 21] [Not Prime ] - it works;<br /><br />2. If we get "21" from a floating-point arithmetic formula such as:<br />= (2^6 - 1) / (27^(1/3)) [Prime ] - Deepanshu's formula may fail in some (if not all) versions of Excel.<br />A formula like this can result in Real values from 20.9999999999993 to 21.0000000000003 - even if the test cell shows "21". In the 2019 and 365 versions of Excel, we can verify it in the formula field ( fx ) when we highlight the MOD(...) function up to its closing parentheses - the results of the function's dynamic array will appear in a white band with Real numbers (as remainders of MOD divisions) making the prime number formula fail.<br /><br />Solutions:<br />1. You can continue using Deepanshu's original formula as long as in any arithmetic operation, the result has its decimal part truncated with INT(...) or rounded with ROUND(...; 0).<br />Examples:<br />= INT( (2^6 - 1) / (27^(1/3)) ) or<br />= ROUND( (2^6 - 1) / (27^(1/3)); 0 )<br />The result will be "21" without any inaccuracy and then [Not Prime ];<br /><br />2. You can use an alternative formula:<br />2A. That truncates the decimal part of the tested number:<br />= IF( AND( MOD( INT(B4); ROW( OFFSET($A$2; ; ; ROUNDUP( SQRT(B4); 0 ) - 1) ) ) <> 0 ); "Prime"; "Not Prime" )<br /><br />2B. And also truncate the Square Root Limit to improve performance in software known for memory-hogging, speed limitations, and inaccuracies - this is what I use in my research in Excel 365:<br />= IF( AND( MOD( INT(B4); ROW( OFFSET($A$2; ; ; INT( SQRT(B4) )) ) ) <> 0 ); "Prime"; "Not Prime" )<br /><br />Remember that these formulas confirm numbers such as 23.01, 23.5, or 23.9999 as [Prime ], number "23". In cases of dealing with INTEGER numbers only under primality testing, such as mine, this formula is satisfactory: precise and fast. The primality test for "2" has been removed to increase performance - just as "1" causes the #REF reference error in Deepanshu's original formula, "2" will do the same in these alternative formulas - but I think we all know that "2" is prime - optionally the IF can be added to the "2" test involving any of the formulas above - see below;<br /><br />3. Primality test of "2"... and "1":<br />3A. Is "2" prime?<br />= IF( B4 = 2; "Prime"; [one of the formulas above HERE] )<br /><br />3B. Is "1" prime? YES, some Mathematicians believe so. Although it cannot be used in the Eratosthenes Sieve nor in the Gaussian density formula, "1" fulfills the most basic requirements of primality in a self-sufficient way. I humbly consider myself in this group of believers - if you also consider yourself the same, then:<br />= IF( OR(B4 = 1; B4 = 2); "Prime"; [one of the formulas above HERE] )<br /><br />Axelhttps://www.blogger.com/profile/16057691062779352412noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-30963505867389100202021-05-30T09:33:07.657-07:002021-05-30T09:33:07.657-07:00From Malinborn asnswer: The reason it is not worki...From 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.alfian-whttps://www.blogger.com/profile/06737717855782995389noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-64133821743685021622021-05-24T12:57:58.199-07:002021-05-24T12:57:58.199-07:00The reason it is not working for some people is, I...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.Malinbornhttps://www.blogger.com/profile/04034201115744165370noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-60721202590184033822021-01-22T13:30:29.371-08:002021-01-22T13:30:29.371-08:00Very clever. the AND function with a single Logica...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.Bubba 'Yackhttps://www.blogger.com/profile/10400103117325835687noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-33675595883412736242020-06-22T01:26:44.706-07:002020-06-22T01:26:44.706-07:00Why test for the square root again? It's alrea...Why test for the square root again? It's already included in the virtual array. <br />When 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. <br />As long as you make it an array formula with Ctrl+Shift+Enter, this modification won't change anything.Andrewhttps://www.blogger.com/profile/07597869303241688241noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-9155535225118175302020-06-21T23:55:47.209-07:002020-06-21T23:55:47.209-07:00The correct formula should be like this:
=IF($C$4...The correct formula should be like this: <br />=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"))euelvishttps://www.blogger.com/profile/06620940822548663532noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-69430119804179833862020-05-24T16:02:56.838-07:002020-05-24T16:02:56.838-07:00it says not prime for 1681 in my case
it says not prime for 1681 in my case<br />Sulamanhttps://www.blogger.com/profile/05039172303879319841noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-5505461593511263102020-04-27T16:53:40.307-07:002020-04-27T16:53:40.307-07:00The formula creates a virtual array from 2 (which ...The 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.<br /><br />Next, every function the array is passed to acts on every entry in that array until it is summarized by the AND function.<br />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.<br />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".<br /><br />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). Andrewhttps://www.blogger.com/profile/07597869303241688241noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-89846300042312004952020-04-27T10:50:47.349-07:002020-04-27T10:50:47.349-07:00This Formula is not correct because for 21 it also...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 brotherMuhammad Hassanhttps://www.blogger.com/profile/17085462098926489432noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-48440749125735146532020-04-16T17:29:58.344-07:002020-04-16T17:29:58.344-07:00Thanks, this formula works completely fine with me...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"))Shreyahttps://www.blogger.com/profile/02363939777240926658noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-13906146032677989612019-11-18T07:50:59.036-08:002019-11-18T07:50:59.036-08:00The formula works, the most common mistake is not ...The 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). Andrewhttps://www.blogger.com/profile/07597869303241688241noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-87814995093876350442019-11-13T05:46:06.385-08:002019-11-13T05:46:06.385-08:00try 1681 it says its prim but 41^2 = 1681
try 1681 it says its prim but 41^2 = 1681<br />chris farmerhttps://www.blogger.com/profile/12914158173497352710noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-8442830819627372002019-10-16T08:23:25.986-07:002019-10-16T08:23:25.986-07:0074 Prime
75 Not Prime
76 Not Prime
77 Not Prime
78...74 Prime<br />75 Not Prime<br />76 Not Prime<br />77 Not Prime<br />78 Not Prime<br />79 Prime<br />80 Not Prime<br />81 Not Prime<br />82 Prime<br />83 Prime<br />84 Not Prime<br />85 Not Prime<br />86 Prime<br />87 Not Prime<br />88 Not Prime<br />89 Prime<br />90 Not Prime<br />91 Not Prime<br />92 Not Prime<br />93 Not Prime<br />94 Prime<br />UNKNOWNhttps://www.blogger.com/profile/12437561717128712767noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-31375139556513624502019-07-17T06:36:50.755-07:002019-07-17T06:36:50.755-07:00Thank you that is genius, works fine for me, shows...Thank you that is genius, works fine for me, shows 6 and 9 as prime. Mark Wadsworthhttps://www.blogger.com/profile/07733511175178098449noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-23916881039158428032019-03-23T11:38:28.780-07:002019-03-23T11:38:28.780-07:00Shows 6 as a prime, not correctShows 6 as a prime, not correctAnonymoushttps://www.blogger.com/profile/06150507672197265790noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-31012828008197920442019-01-05T07:58:18.743-08:002019-01-05T07:58:18.743-08:00It works fine for me. Make sure to press CTRL + SH...It 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<br />{=IF(B4=2,"Prime",IF(AND(MOD(B4,ROW(OFFSET($A$2,,,ROUNDUP(SQRT(B4),0)-1)))<>0),"Prime","Not Prime"))}<br />note the {} brackets.<br />Also note that entering the brackets manually does NOT work; you have to press CTRL + SHIFT + ENTER to get the brackets.Michaelhttps://www.blogger.com/profile/16769280621950269831noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-26123825515511066772018-07-23T19:21:04.500-07:002018-07-23T19:21:04.500-07:00Careful using this with numbers 81 and larger... Careful using this with numbers 81 and larger... Anonymoushttps://www.blogger.com/profile/07902923162209164375noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-31421292256629267652017-09-12T00:54:32.071-07:002017-09-12T00:54:32.071-07:00Clever!! I never would have thought of using an ar...Clever!! 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!<br />Though, 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.Andrewhttps://www.blogger.com/profile/07597869303241688241noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-35027662014045156492017-03-16T11:00:47.384-07:002017-03-16T11:00:47.384-07:00It says 'Not Prime' for 111,111,111. You c...It says 'Not Prime' for 111,111,111. You can save your workbook to any cloud drive and share link with me to debug.Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-22474975348467970072017-03-15T14:25:33.362-07:002017-03-15T14:25:33.362-07:00Doesn't seem to be 100% accurate, for example,...Doesn'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.portalsrule123noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-66521693155094055792016-09-14T15:22:59.465-07:002016-09-14T15:22:59.465-07:00Anonymous, it is very easily copied into any workb...Anonymous, 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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-72442545832181533402016-03-15T07:48:22.120-07:002016-03-15T07:48:22.120-07:00Your formula references A2, but doesn't say wh...Your 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. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-36891155280772715282016-01-17T13:48:56.365-08:002016-01-17T13:48:56.365-08:00It is working fine for 9. Download the workbook (l...It is working fine for 9. Download the workbook (link provided in the article)Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.com