| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prime Number just for fun
Hello --
I found this on the Internet and would like to make it work. I placed the formula in cell B1 and the number to be examined in A1 however it evaluates primes and non-primes as TRUE. Obviously, I have done something wrong. Can anyone steer me correctly? Paul POWER FORMULA TECHNIQUE by Bob Umlas This array formula returns TRUE if the number in cell A1 is a prime number. =OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))= INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0))) Use it as a conditional formatting formula, with A1 as the active cell in the selection to be formatted. Here's how Bob's amazing formula works. In a nutshell, the number is divided by all potential prime factors, and the resulting array is tested to see whether it contains a whole number. If is does, you have a prime number. A limitation of this formula is that it cannot test numbers that are greater than 65535^2. This is due to the array size constraint in Excel 97/2000. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
Used as a cell formula it needs to be confirmed with CTRL+SHIFT+ENTER so that braces like these { } appear around the formula in the formula bar. If you already have the formula in B1 then simply put the cursor somewhere in the formula within the formula bar and hit CTRL+SHIFT while holding down ENTER.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
I would hold down the Ctrl+Shift Keys and hit the Enter key. When I try it
"daddys" way, the cursor travels away from the cell. -- Regards, Tom Ogilvy "daddylonglegs" wrote in message news:daddylonglegs.24l36y_1142212502.4088@excelfor um-nospam.com... Used as a cell formula it needs to be confirmed with CTRL+SHIFT+ENTER so that braces like these { } appear around the formula in the formula bar. If you already have the formula in B1 then simply put the cursor somewhere in the formula within the formula bar and hit CTRL+SHIFT while holding down ENTER.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
When I hold down the ENTER key, the cursor takes off down the sheet.
Would there be a way of placing this in a module and coding it? Thanks, Paul =========== "daddylonglegs" wrote in message news:daddylonglegs.24l36y_1142212502.4088@excelfor um-nospam.com... Used as a cell formula it needs to be confirmed with CTRL+SHIFT+ENTER so that braces like these { } appear around the formula in the formula bar. If you already have the formula in B1 then simply put the cursor somewhere in the formula within the formula bar and hit CTRL+SHIFT while holding down ENTER.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
Hello -
I had the same thing happen on my machine - when I hold down the ENTER key, the cursor flies down the sheet. I asked "Daddylonglegs" if there was a way of coding this so one need only enter a number to be evaluated in cell A1 and when the cursor was moved to another cell an update function (or something) would evaluate A1. Thanks, Paul -------------- "Tom Ogilvy" wrote in message ... I would hold down the Ctrl+Shift Keys and hit the Enter key. When I try it "daddys" way, the cursor travels away from the cell. -- Regards, Tom Ogilvy "daddylonglegs" wrote in message news:daddylonglegs.24l36y_1142212502.4088@excelfor um-nospam.com... Used as a cell formula it needs to be confirmed with CTRL+SHIFT+ENTER so that braces like these { } appear around the formula in the formula bar. If you already have the formula in B1 then simply put the cursor somewhere in the formula within the formula bar and hit CTRL+SHIFT while holding down ENTER.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
you can try this, just change b2 to wherever you want the formula
Range("b2").FormulaArray = _ "=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT(""2:"" & INT(SQRT(A1))))" _ & "=INT(A1/ROW(INDIRECT(""2:"" & INT(SQRT(A1))))),0)))" -- Gary "Paul Breslin" wrote in message ... When I hold down the ENTER key, the cursor takes off down the sheet. Would there be a way of placing this in a module and coding it? Thanks, Paul =========== "daddylonglegs" wrote in message news:daddylonglegs.24l36y_1142212502.4088@excelfor um-nospam.com... Used as a cell formula it needs to be confirmed with CTRL+SHIFT+ENTER so that braces like these { } appear around the formula in the formula bar. If you already have the formula in B1 then simply put the cursor somewhere in the formula within the formula bar and hit CTRL+SHIFT while holding down ENTER.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
Thanks very much - that worked just fine.
Paul ------------ "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... you can try this, just change b2 to wherever you want the formula Range("b2").FormulaArray = _ "=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT(""2:"" & INT(SQRT(A1))))" _ & "=INT(A1/ROW(INDIRECT(""2:"" & INT(SQRT(A1))))),0)))" -- Gary "Paul Breslin" wrote in message ... When I hold down the ENTER key, the cursor takes off down the sheet. Would there be a way of placing this in a module and coding it? Thanks, Paul =========== "daddylonglegs" wrote in message news:daddylonglegs.24l36y_1142212502.4088@excelfor um-nospam.com... Used as a cell formula it needs to be confirmed with CTRL+SHIFT+ENTER so that braces like these { } appear around the formula in the formula bar. If you already have the formula in B1 then simply put the cursor somewhere in the formula within the formula bar and hit CTRL+SHIFT while holding down ENTER.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prime Number just for fun
Sorry guys, just all fingers and thumbs  of course Tom's right......write out 100 times.... I would hold down the Ctrl+Shift Keys and hit the Enter key I would hold down the Ctrl+Shift Keys and hit the Enter key I would hold down the Ctrl+Shift Keys and hit the Enter key I would hold down the Ctrl+Shift Keys and hit the Enter key I would hold down the Ctrl+Shift Keys and hit the Enter key I would hold down the ...... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521585 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Largest Prime Number | Excel Discussion (Misc queries) | |||
| prime number | Excel Discussion (Misc queries) | |||
| how do I find prime factors of a number | Excel Worksheet Functions | |||
| How do I test for a prime number? | Excel Worksheet Functions | |||
| Prime number puzzle | Excel Worksheet Functions | |||
Linear Mode
