#### Prime Numbers

```Hi,
I've been using this formula to identify whether a number is a prime number
or not. It works, but it's a bit long and incongruous.
=IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
than
8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
Prime","Prime"))
Can anyone suggest a better way?
Alan.

``` 0 9/14/2008 11:42:11 PM excel  39879 articles. 2 followers. 6 Replies 817 Views Similar Articles

[PageSpeed] 53

```Try a formula like the following array formula:

=IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),"prime","not prime")

This will return "prime" if A1 is prime or "not prime" if it is not. Note
that this is a array formula, so you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces {}. The formula will not work if you do not enter it with CTRL SHIFT
ENTER. See http://www.cpearson.com/Excel/arrayformulas.aspx for much more

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Alan" <alan111@ntlworld.com> wrote in message
news:ObH3DOsFJHA.2076@TK2MSFTNGP04.phx.gbl...
> Hi,
> I've been using this formula to identify whether a number is a prime
> number or not. It works, but it's a bit long and incongruous.
> =IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
> than
> 8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
> Prime","Prime"))
> Can anyone suggest a better way?
> Alan.

``` 0 9/15/2008 12:50:45 AM
```Just for fun, the following array formula will test for prime twins,
returning "prime", "prime twin", or "not prime".

=IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),IF(OR(AND((MOD(\$A\$1-2,ROW(INDIRECT("2:"&\$A\$1-3)))<>0)),AND((MOD(\$A\$1+2,ROW(INDIRECT("2:"&\$A\$1+1)))<>0))),"prime
twin","prime"),"not prime")

Enter with CTRL SHIFT ENTER.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Chip Pearson" <chip@cpearson.com> wrote in message
news:ekKuW0sFJHA.3848@TK2MSFTNGP02.phx.gbl...
> Try a formula like the following array formula:
>
> =IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),"prime","not prime")
>
> This will return "prime" if A1 is prime or "not prime" if it is not. Note
> that this is a array formula, so you must press CTRL SHIFT ENTER rather
> than just ENTER when you first enter the formula and whenever you edit it
> later. If you do this properly, Excel will display the formula enclosed in
> curly braces {}. The formula will not work if you do not enter it with
> CTRL SHIFT ENTER. See http://www.cpearson.com/Excel/arrayformulas.aspx for
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
>    Excel Product Group
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
>
>
> "Alan" <alan111@ntlworld.com> wrote in message
> news:ObH3DOsFJHA.2076@TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I've been using this formula to identify whether a number is a prime
>> number or not. It works, but it's a bit long and incongruous.
>> =IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
>> than
>> 8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
>> Prime","Prime"))
>> Can anyone suggest a better way?
>> Alan.
>

``` 0 9/15/2008 1:15:43 AM
```In article <e8fkTCtFJHA.4460@TK2MSFTNGP06.phx.gbl>, "Chip Pearson" <chip@cpearson.com> wrote:
>Just for fun, the following array formula will test for prime twins,
>returning "prime", "prime twin", or "not prime".
>
>=IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),IF(OR(AND((MOD(\$A\$1-2,ROW(IN
>DIRECT("2:"&\$A\$1-3)))<>0)),AND((MOD(\$A\$1+2,ROW(INDIRECT("2:"&\$A\$1+1)))<>0))),"p
>rime
>twin","prime"),"not prime")
>
>Enter with CTRL SHIFT ENTER.

... and in case anyone else is interested, a 'prime twin' or 'twin prime' is
2 prime numbers that differ by 2 (e.g. 5 and 7; 11 and 13) - the smallest
number they can differ by (one exception ... 2 and 3).
Thanks ... a fascinating formula ... and I learned something today. :)

``` 0 9/15/2008 4:22:06 AM
```Perhaps have a look at

http://www.dailydoseofexcel.com/archives/2005/06/30/is-this-number-prime/

--
met vriendelijke groetjes

freddy.van.looy@telenet.be
"Alan" <alan111@ntlworld.com> schreef in bericht
news:ObH3DOsFJHA.2076@TK2MSFTNGP04.phx.gbl...
> Hi,
> I've been using this formula to identify whether a number is a prime
> number or not. It works, but it's a bit long and incongruous.
> =IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
> than
> 8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
> Prime","Prime"))
> Can anyone suggest a better way?
> Alan.

``` 0 9/15/2008 6:44:34 AM
```The problem with your algorythm is that if you square a prime, you get a number divisible only by 1, the prime and the squared number itself.  (Ex. 11 squared is 121) This number will not be found by your application, but is NOT a prime.  What you need to add is a line to check whether or not the square root of the number is equal to the integer of the square root.  If not, then, together with your process, you'll be fine.

Alan wrote:

Prime Numbers
14-Sep-08

Hi,
I've been using this formula to identify whether a number is a prime number
or not. It works, but it's a bit long and incongruous.
=IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
than
8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
Prime","Prime"))
Can anyone suggest a better way?
Alan.

On Sunday, September 14, 2008 7:42 PM
Alan wrote:

Prime Numbers
Hi,
I've been using this formula to identify whether a number is a prime number
or not. It works, but it's a bit long and incongruous.
=IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
than
8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
Prime","Prime"))
Can anyone suggest a better way?
Alan.

On Sunday, September 14, 2008 8:50 PM
Chip Pearson wrote:

Try a formula like the following array
Try a formula like the following array formula:

=IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),"prime","not prime")

This will return "prime" if A1 is prime or "not prime" if it is not. Note
that this is a array formula, so you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces {}. The formula will not work if you do not enter it with CTRL SHIFT
ENTER. See http://www.cpearson.com/Excel/arrayformulas.aspx for much more

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Alan" <alan111@ntlworld.com> wrote in message
news:ObH3DOsFJHA.2076@TK2MSFTNGP04.phx.gbl...

On Sunday, September 14, 2008 9:15 PM
Chip Pearson wrote:

Just for fun, the following array formula will test for prime twins, returning
Just for fun, the following array formula will test for prime twins,
returning "prime", "prime twin", or "not prime".

=IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),IF(OR(AND((MOD(\$A\$1-2,ROW(INDIRECT("2:"&\$A\$1-3)))<>0)),AND((MOD(\$A\$1+2,ROW(INDIRECT("2:"&\$A\$1+1)))<>0))),"prime
twin","prime"),"not prime")

Enter with CTRL SHIFT ENTER.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Chip Pearson" <chip@cpearson.com> wrote in message
news:ekKuW0sFJHA.3848@TK2MSFTNGP02.phx.gbl...

On Monday, September 15, 2008 12:22 AM
bruce.sinclai wrote:

Re: Prime Numbers
In article <e8fkTCtFJHA.4460@TK2MSFTNGP06.phx.gbl>, "Chip Pearson" <chip@cpearson.com> wrote:

... and in case anyone else is interested, a 'prime twin' or 'twin prime' is
2 prime numbers that differ by 2 (e.g. 5 and 7; 11 and 13) - the smallest
number they can differ by (one exception ... 2 and 3).
Thanks ... a fascinating formula ... and I learned something today. :)

On Monday, September 15, 2008 2:44 AM
Frederik wrote:

Re: Prime Numbers
Perhaps have a look at

http://www.dailydoseofexcel.com/archives/2005/06/30/is-this-number-prime/

--
met vriendelijke groetjes

freddy.van.looy@telenet.be

Submitted via EggHeadCafe - Software Developer Portal of Choice
Creating a WPF Custom Control
``` 0  George
2/19/2010 4:53:19 PM
```You are responding to a post that's a year and a half old.

Jordon

George McCabe wrote:
> The problem with your algorythm is that if you square a prime, you get a number divisible only by 1, the prime and the squared number itself.  (Ex. 11 squared is 121) This number will not be found by your application, but is NOT a prime.  What you need to add is a line to check whether or not the square root of the number is equal to the integer of the square root.  If not, then, together with your process, you'll be fine.
>
>
>
> Alan wrote:
>
> Prime Numbers
> 14-Sep-08
>
> Hi,
> I've been using this formula to identify whether a number is a prime number
> or not. It works, but it's a bit long and incongruous.
> =IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
> than
> 8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
> Prime","Prime"))
> Can anyone suggest a better way?
> Alan.
>
> Previous Posts In This Thread:
>
> On Sunday, September 14, 2008 7:42 PM
> Alan wrote:
>
> Prime Numbers
> Hi,
> I've been using this formula to identify whether a number is a prime number
> or not. It works, but it's a bit long and incongruous.
> =IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater
> than
> 8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not
> Prime","Prime"))
> Can anyone suggest a better way?
> Alan.
>
> On Sunday, September 14, 2008 8:50 PM
> Chip Pearson wrote:
>
> Try a formula like the following array
> Try a formula like the following array formula:
>
> =IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),"prime","not prime")
>
> This will return "prime" if A1 is prime or "not prime" if it is not. Note
> that this is a array formula, so you must press CTRL SHIFT ENTER rather than
> just ENTER when you first enter the formula and whenever you edit it later.
> If you do this properly, Excel will display the formula enclosed in curly
> braces {}. The formula will not work if you do not enter it with CTRL SHIFT
> ENTER. See http://www.cpearson.com/Excel/arrayformulas.aspx for much more
>
>

``` 0 2/19/2010 6:48:38 PM Similar Artilces:

Does anyone of a free service to send you a email when a change in the prime rate occurs. The Fed meets every six weeks; find the next one and get in sync! ...

Numbers #2
I am using Office 2003 and am trying to do an expense sheet for a new business .I am doing a sheet for each month and need to put numbers in first column for each day 1-30 and 1-31. I have used Excel in the past and remember highlighting the first three numbers and dragging the rest to complete but it's not working. HELP -- sleepydee You're close. Instead of dragging by clicking on the cell's outline, click on the little black square at the bottom right corner of the outline. Szalapski -- TommySzalapski ---------------------------------------------------------------...

Formatting footnotes when page numbering is turned on
When I have paragraph numbering turned on, Word insists on assigning a paragraph number to my footnotes as well as a footnote number. I can manually delete the paragraph number, but would prefer to turn off the option. More important, however, is that Word also assigns a paragraph number to the line drawn across the page above the footnotes, and I cannot find a way to remove this. Can somebody offer me a suggestion on what to do? It would appear that you have applied numbering to the Normal style, which affects every style based on it. This is one reason we advocate using Bod...

In excel how do i put zero as the first Number in a cell
how could i add zeros as the first number in a cell without a decimal Hi Two ways: Start your entry with an ', to read '083, or Format the cells where you want to insert this as text, and then enter the number "help" wrote: > how could i add zeros as the first number in a cell without a decimal > Either format the cell for TEXT (Right-click > FormatCells > NumberTab > Text > ok), or if you only have a few, just type a leading apostrophe before the leading zero.........it won't show up and will change that cell to TEXT format.......... Vaya con ...

Prime Numbers
Hi, I've been using this formula to identify whether a number is a prime number or not. It works, but it's a bit long and incongruous. =IF(A1<8,"2, 3, 5, and 7 are prime numbers. Please enter a number greater than 8",IF(OR(MOD(\$A\$1,2)=0,MOD(\$A\$1,3)=0,MOD(\$A\$1,4)=0,MOD(A1,5)=0,MOD(A1,6)=0,MOD(A1,7)=0,MOD(A1,8)=0,MOD(A1,9)=0),"Not Prime","Prime")) Can anyone suggest a better way? Thanks in advance, Alan. Try a formula like the following array formula: =IF(AND((MOD(\$A\$1,ROW(INDIRECT("2:"&\$A\$1-1)))<>0)),"prime","no...

mapping prime contact
Hi. I have in the 'Contact' an attribute level {Prime, Secondary} for the contact person. I need to display the primary contact person in the lookup field on the Account entity, so the user does not have to dive into the TreeView to open the 'Contacts' in order to see the one who has the level=Prime attribute. How do I do that (the lookup in the Account entity is merely a caption and a 1:N relationship to the whole contact entity). Thanks On May 7, 9:51=A0am, 4crm4 <4c...@discussions.microsoft.com> wrote: > Hi. > I have in the 'Contact' =A0an attribute...

Maximum numbers in Outlook
Does anyone know the maximum number of names and appointments that can be held in the Outlook diary. I am trying to set up an appointment diary with it, but at times when a new appointment/name is added others are deleted. I guess I may be exceeding it's limit, but not sure ...

CELL/NUMBER FORMAT
EXCEL. When entering a whole number into a cell EXCEl is dividing that number by 10 immediatly after entry. If I place a decimal point after the number this does not happen. If I enter a number less than 1 the number is entered normally. It appears that EXCEL needs a decimal point to enter the number correctly. I have used EXCEL for many years (current version from 2004) and this has never hapenned. Any Ideas out there guys? ...

Stored Procedure Priming
Hello, I am getting the following error. Have been for some time. The job fails with the following errors below. The job runs under the local Windows Admin account wich is an owner of the DB. I am not a newbie to SQL neither am I proficient. Your help is appreciated. The server is Windows Server 2003 Premium running Exchange and MS CRM 3.0 Job Paramters Step Name: Execute Priming Stored Procedure Type: TSQL Database: Master Command: exec p_PrimeCascadeWrapper Event Error Event Type: Warning Event Source: SQLSERVERAGENT Event Category: Job Eng...

number of characters in POP
Hello: This client wants to be able to expand the number of characters for the item description in PO Entry so that more characters will print on the purchase order. But, when I go into Modify Current Window, I cannot find the field for the item's description nor am I able to tell if it is possible to increase the number of characters for this field. Does anyone have any ideas on this? Perhaps, it's not possible to simply do this with Modifier. Thanks! John John, It is not possible to modify the length of the description field. However this is what I suggest. a. You could...

Outline Numbering
I'm trying to outline number a report and have followed the guidance on Shauna Kelly's page. However, my numbering is back to front - so it goes: 1 1.1 1.1.1 1.2 2.1.1 Instead of 1.2 and 1.2.1. When I go into 'level 2' numbering, I can't link this to the previous level - it selects it, then unselects it by itself. If I clear level 2 and start again by selecting 'number style 1,2,3' then choose 'previous level number - 1' it puts the numbers together (11) and unselects the link to the previous level. And when I put a 'period' in it...

Number of Columns
Dear all, We are using Excel 97, and we need to know if there is possible add more columns than the 256 that already are available from A - IV. We need that because we are exporting data from one aplication to Excel. Thanks a lot. Regards, Pedro Mestre no, 256 is the limit. you may be able to use ms query to 'query' the data source and select only the colums you need, or to build separate queries that return the data over multiple sheets No, 256 is all there are, Regards, Alan. "Pedro Mestre" <PedroMestre@discussions.microsoft.com> wrote in message news:0FBBBE2...

number format
hello my numbers are formatted using question marks, so i can center them and still justify them by the decimal point. for ex. a column of numbers with 2345678 being the largest of them, will be formatted with the string: ?,???,??0;-?,???,??0;- this way i can center the column, and all numbers will straighten to the right, by the decimal point. zeros appear as hyphens. the problem is with the minus signs. if on the previous example i will have -2345, it will look like this: - 2,345 (the minus sign away from the digits). any suggestions? i still wish to center the column. if i give this ...

Determine the number of bits in a negative number
I'd like to determine the number of bits (the position of the highest on bit) in a negative number (say -1) without using knowledge of its type. Valid operations would be AND, OR, XOR, negation, and one's compliment can be done as: 'Get a -1 of the same type as myvar Dim minus1: minus1 = myvar - (myvar + 1) Dim onesComp: onesComp = myvar XOR minus1 You could also use multiplication or division by 2 or other mathematical operators/functions, but then sign extension comes into play. The standard solution is to presume the answer given the type of the variable, but...

Pivot Table
Hi, I have a Pivot table with some numbers in it. Is there a way to replace the numbers with a text? For example: replace all numbers >1 with "yes" . Is this possible? If not whats the best way to achieve this? Regards, Vinstream Only by changing your data source. Eg. Sub Test() Dim rCell As Range For Each rCell In Range("DataSource").Columns _ (2).SpecialCells(xlCellTypeConstants, xlNumbers).Cells If rCell > 1 Then rCell = "yes" Next rCell End Sub "vinstream" <vinstream@gmail.com> wrote in mes...

Page Numbers #2
I have a 20 page booklet and am trying to get the page number to not show on the last page which also is the back cover of the booklet. Can anyone help? >-----Original Message----- >I have a 20 page booklet and am trying to get the page >number to not show on the last page which also is the back >cover of the booklet. Can anyone help? >. > Go to last page, then under view on the tool bar click on ignore master page. ...

[Fwd: Re: How to not copy the numbering from numbered list in Word ?]
FYI By temporarily disabling the numbers and bullets in word before you copy and paste the text. Then you can simply close the word document (without saving it) so that no changes in the word file are saved. That one time call girl may suggest that you should use Tipex on the screen to remove the numbers!!! You have been warned. hth dingdongdingding wrote: > > Hi ! > I want to copy a numbered list in Word and paste to a text file. > The numbers show up. How do I not copy the numbers preceding the > items ? > > Thanks very much in adv...

Changing number to dates
When ever I try to paste the characters 6-16 or 4-14 for example it is displayed as a date. I have tried formatting the cells to read this information as text. it is always overridden and converted to a date. How do I paste this info and have it appear the way that I want? Your help will be greatly appreciated. Hi Craig format the target cells as 'Text' or insert the values with a leading apostrophe (') Frank Craig wrote: > When ever I try to paste the characters 6-16 or 4-14 for example it > is displayed as a date. I have tried formatting the cells to read > this info...

column with numbers
Hi! Suppose, I have a two (or several) columns with numbers. I want to analyze two or more columns in a different way, for instance I want to know the growth in percentage and probably have some visual interpretation (i.e. arrow up or down). Is there an Excel plug-in or stand-alone software for this? If anyone expirienced the same problem? I'm going to look for such a software, if one have in mind some other "must-have" features that I missed? Thank you! bob.elliott@aks-labs.com wrote: > Hi! > > Suppose, I have a two (or several) columns with numbers. I want to >...

Auto Numbering #5
Can any one help me with this? I am trying to number a cost tabl sequencially from A.1.1, A1.1.2 and so on. Can you do this in Excel And can you make to where if you insert a row in between the rows tha are already numbered that it changes everything automatically? Pleas Help!! -- jharkin ----------------------------------------------------------------------- jharkins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2565 View this thread: http://www.excelforum.com/showthread.php?threadid=39099 Using Excel 2000, I entered A.1.1 in a cell. Then I used my mouse a...

2007 Not Ready for Prime Time
Just a quick note that, IMHO, Excel 2007 is not ready for normal usage, particularly on XP (where it behaves oddly, e.g., fails to open up XLS files when you double-click them; it works better in Vista). But the biggest problem is that Excel 2007 interoperates very poorly with previous versions of Excel. I have some moderately large 2007 models (e.g., about 1MB on disk), and when I try to Save As to downrev them so that users of earlier versions of Excel can access them, it takes bloody darn forever. In fact, I don't know how long it takes because I've yet to actually let it finish (a...

Numbers won't display in report
Access 2000 on XP Home w/all current updates, Office 2K also installedWhen I run a report on either a table or query, my fields which onlycontain numbers will not display. Fields containing text displaycorrectly.I've changed the field format(s) back and forth between text andnumber with no change in output.If I add a text character to the field containing a number, (example: A305) only the "A" shows.Even the page numbers and date on the report page do not display.If I output the report to Word or Excel, all the data, includingnumbers are visible.I have uninstalled and re-installe...