You are not logged in.
Pages: 1
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)))
Offline
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
Offline
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
Offline
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
Offline
Pages: 1