Prime Number just for fun

[Paul Breslin]

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.

[daddylonglegs]

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....

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Paul Breslin]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Paul Breslin]

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" <[email protected]> wrote in message
news:[email protected]...
>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"
> <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[Gary Keramidas]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[Paul Breslin]

Thanks very much - that worked just fine.
Paul
------------


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>>>
>>> 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
>>>
>>
>>
>
>

[daddylonglegs]

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 ......