• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel and primenumbers

E

Excelz

New Member
Hey, sorry if this has been asked before, but I couldn't seem to find a post for it.


I'm trying to use this array formula which I founded on this site, that tells if a number is a prime or not =IF(MIN(MOD($B$5,ROW(INDIRECT("2:"&INT(SQRT($B$5))))))=0,"not prime","prime"). I just can't get it to work though, whenever I enter the formula excel marks the part saying "ROW". I'm using a Swedish version of excel, if that matters. I even tried to translate the commands in the formula into the Swedish ones, but with no success. I would be extremely happy if anyone could help me with this.


Thanks
 
Apart from the translation issues

Some Excel versions use ; instead of ,

ie: =IF(MIN(MOD($B$5;ROW(INDIRECT("2:"&INT(SQRT($B$5))))))=0;"not prime";"prime")

Are you entering it as an Array Formula with Ctrl Shift Enter
 
I tried your formula and did edit the cells to $B$3, that's about it. Now I got the problem "NAME?" instead. And yeah I'm hitting Ctrl Shift Enter after pasting the formula into the bar.


Appreciating the help!
 
Which function of the formula is XL not recognizing (the reason for NAME error)?
 
I'm not sure where to check out which part of the formula excel not recognizing. When pressing "Show steps" excel just keeps underlining most of the formula.
 
Might be this will help you.


In Microsoft Excel the names of functions depend on the language of the installed version of MS-Office. Here's a link containing the Excel function names in 16 languages.


http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html
 
Great site, thanx Meedan


Excelz, try:

=OM(MIN(REST($B$5,ROW(INDIREKT("2:"&HELTAL(ROT($B$5))))))=0,"not prime","prime")
 
Wow, thanks everyone for your help. The formula ended up like this =OM(MIN(REST($B$3;RAD(INDIREKT("2:"&HELTAL(ROT($B$3))))))=0;"not prime";"prime")


Translating the functions thanks to Meedan and Hui and replacing the "," with ";" like Hui said worked great!


One somewhat of a crucial problem though. Like you can see in the formula I've changed the cell to $B$3 where I got the number 3 in it. When using the formula it says that the number 3 is not a prime number, which I believe it is.
 
Correct. The problem is that to test for primes, the formula is dividing the original number by every number between 2 and the integer of the square root. However, the integer square root of 3 (and 2, also prime) is 1. The mod function then does 3/ 1, which has a 0 remainder and thinks the number is not prime. I suppose you could add a small IF at the beginning, to check for these 2 exceptions:


=OM(ELLER($B$3=2,$B$3=4),"prime",OM(MIN(REST($B$3;RAD(INDIREKT("2:"&HELTAL(ROT($B$3))))))=0;"not prime";"prime"))


English:

=IF(OR($B$3=2,$B$3=3),"prime",IF(MIN(MOD($B$5,ROW(INDIRECT("2:"&INT(SQRT($B$5))))))=0,"not prime","prime"))
 
Here's an alternative I found on the Web some time ago. I can't remember where but it may have been Chip Pearson's site.

Code: 
=IF(A1=1,"Neither",IF(A1,OR(A1={1,2,3},AND(MOD(A1,ROW(INDIRECT("2:"&INT(A1^0.5))))))+0,""))
 
You must log in or register to reply here.
Back
Top