Math Is Fun Forum

Euclid

Member

Registered: 2008-04-07

Posts: 1

Identifying primes in a spreadsheet

I have this algorithm that allows conditional formatting in Excel.  If the cell contains a prime number then the cell's background colour is blue.  If it is composite it is white.  It is really handy.  I did not write it and I want to port it over to run on Open Office's Calc but it does not work.  It only identifies 2 and 3 as prime.  Anyone any ideas why it fails?

Here it is.  I think the original author of this is  Bob Umlas.  Quite an amazing piece of code.

=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

John E. Franklin

Member

Registered: 2005-08-29

Posts: 3,588

Re: Identifying primes in a spreadsheet

The MATCH function is slightly different because in OpenOffice it returns the position in the array, not the actual data.  Note that it loops from 2 to 6 if number is say 37, and divides 37 like 37/2, 37/3, 37/4, 37/5, 37/6.
It compares those answers to the truncated (INT) format of those fractions.
the MATCH(?,?,0), the zero means an exact match, not above or below like the Price-Is-Right-Show.

Last edited by John E. Franklin (2008-04-08 05:04:23)

---

igloo myrtilles fourmis

John E. Franklin

Member

Registered: 2005-08-29

Posts: 3,588

Re: Identifying primes in a spreadsheet

I would try taking out the "INDIRECT" part and see if it loops 2 to 6 for 37, since
square root of 37 is 6.08.  The indirect part seems like it doesn't need to be there.

=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW("2:"&INT(SQRT(A1)))=INT(A1/ROW("2:"&INT(SQRT(A1)))),0)))

try that line above, just a guess.  The original website by Bob U. I saw he doesn't explain it yet.

---

igloo myrtilles fourmis

John E. Franklin

Member

Registered: 2005-08-29

Posts: 3,588

Re: Identifying primes in a spreadsheet

I got some help at the openOffice forum.

http://www.oooforum.org/forum/viewtopic.phtml?t=70296&sid=77e659e1a6e43dab2516e42ce214f804

or click
http://www.oooforum.org/forum/viewtopic … 2ce214f804

---

igloo myrtilles fourmis