| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the prime numbers?
Does anyone have any suggestions on how to determine the prime numbers?
There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
This URL has the VBA code for creating a UDF that will determine if a number
is prime or not. http://www.bluemoosetech.com/microso... me%20Numbers -- Kevin Backmann "Eric" wrote: Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
set up a list with primes and then use index match to find the one above and
below your number "Eric" wrote: Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
No need for VBA this time:
=IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") must be array-entered (ie with SHIFT+CTRL+ENTER) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Eric" wrote in message ... Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
Hi Bernard
Nice solution, but I think it works without being array entered. -- Regards Roger Govier "Bernard Liengme" wrote in message ... No need for VBA this time: =IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") must be array-entered (ie with SHIFT+CTRL+ENTER) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Eric" wrote in message ... Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
Eric,
To find the next higher prime, use the UDF below (Copy the code and paste it into a codemodule) like this: =NextPrime(A2,TRUE) and to find the next lower prime, use it like this: =NextPrime(A2,FALSE) HTH, Bernie MS Excel MVP Option Explicit Function NextPrime(inRange As Long, Increase As Boolean) As Variant Dim i As Long Dim j As Long Dim boolSolved As Boolean Dim isPrime As Boolean boolSolved = False i = inRange While Not boolSolved i = i + IIf(Increase, 1, -1) If i < 2 Then NextPrime = "No prime is less than " & inRange Exit Function End If If i Mod 2 = 0 Then If i = 2 Then NextPrime = 2 Exit Function Else i = i + IIf(Increase, 1, -1) End If End If isPrime = True For j = 3 To i ^ 0.5 Step 2 If i Mod j = 0 Then isPrime = False End If Next j If isPrime Then NextPrime = i Exit Function End If Wend End Function "Eric" wrote in message ... Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
Bernard,
That formula works to determine if the entered number is prime, but the OP wants the first prime that is higher, and the first prime that is lower. HTH, Bernie MS Excel MVP "Bernard Liengme" wrote in message ... No need for VBA this time: =IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") must be array-entered (ie with SHIFT+CTRL+ENTER) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Eric" wrote in message ... Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
I know but I couldn't help showing my solution!
That formula that even impressed Bob Umlas Please forgive me showing off!!! best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bernard, That formula works to determine if the entered number is prime, but the OP wants the first prime that is higher, and the first prime that is lower. HTH, Bernie MS Excel MVP "Bernard Liengme" wrote in message ... No need for VBA this time: =IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") must be array-entered (ie with SHIFT+CTRL+ENTER) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Eric" wrote in message ... Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
True, I was working from memory (core needs replacing)
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi Bernard Nice solution, but I think it works without being array entered. -- Regards Roger Govier "Bernard Liengme" wrote in message ... No need for VBA this time: =IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") must be array-entered (ie with SHIFT+CTRL+ENTER) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Eric" wrote in message ... Does anyone have any suggestions on how to determine the prime numbers? There is a given number 10 in cell A2, I would like to determine the next prime numbers below and over this given number 10. In this case, 11 will be returned in cell A1, and 7 will be returned in A3. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the prime numbers?
I put this formula in cell B1. It returns Not Prime for every number entered
in A1. I tried both normal and array. I copied the formula from B1 and pasted it here. =IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") Any ideas why it doesn't work? "Bernard Liengme" wrote in message ... No need for VBA this time: =IF(SUMPRODUCT(--(MOD(A1,(ROW(INDIRECT("2:"&INT(SQRT(A1))))))=0))," Not Prime","Prime") must be array-entered (ie with SHIFT+CTRL+ENTER) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to determine the top 5 occurrences from a list of numbers? | Excel Discussion (Misc queries) | |||
| How to determine the formula to generate following numbers? | Excel Discussion (Misc queries) | |||
| How to determine the frequency of a list of numbers? | Excel Discussion (Misc queries) | |||
| how to determine if a series of numbers contain odd or even values | Excel Worksheet Functions | |||
| How do I determine which numbers in a list equal a given sum? | Excel Discussion (Misc queries) | |||
Linear Mode
