Prime and Composite numbers

Dear Excel Experts,

I have numbers in A2, B2, C2, D2, E2, F2 and would like to count how many Prime and Composite numbers are there. For instance 2, 3, 4, 5, 6, 7 = 4/2

And also would like to know all numbers put together is Prime or Composite. For Instance, 2, 3, 4, 5, 6, 7 =Composite (because all numbers put together is 234567)

Appreciate if somebody could provide me a formula for both the above scenarios.

Thanks in advance

LOOK here---> https://www.listendata.com/2013/05/c...n%20is%20prime.

Originally Posted by joeu2004

In other words, construct all 1, 2, 3, 4, 5 and 6-digit numbers using the digits in A2:F2, and classify them as prime or not (= composite)?

Originally Posted by Gulya

Yes, after using the digits in A2:F2 and classify them as prime or composite

Okay, that helps to clarify. But the example Excel file attached to the OP does little-to-nothing to clarify further. It contains the following.

	A	B	C	D	E	F	G	H
1	A	B	C	D	E	F	Prime/Composite	Prime/Composite
2	2	3	4	5	6	7	4/2	Composite

The "value" in G2 is not a number; instead, it is in the form of a fraction. It is neither prime nor composite. At best, we might interpret your intent to mean "4 divided by 2". But that is 2, which is a prime, not a composite as indicated in H2.

-----

Please provide a table, in one form or another, that demonstrates several concrete examples of how you want to combine several digits from A2:F2 into a number, which would be classified as prime or composite.

Of course, you would fill in the table manually, for now, since you are asking for an Excel (or VBA?) implementation to accomplish it.

For example, would your table look like following?

	A	B	C	D	E	F	G	H
1	digits....						number	prime/composite?
2	2	3	4	5	6	7	64723	composite
3							23	prime
4							354627	composite
5							43257	composite
6							457	prime

You already agreed that the number would be 1 to 6 digits.

Is each randomly-selected cell in A2:F2 used only once in the 1-to-6-digit number in column G? For example, if the digits in A2:F2 are unique, would 23423 be allowed (uses A2 and B2 twice each, by coincidence)?

(But if 2 appears in two cells and 3 appears in two cells, 23423 should be allowed.)

Have tried the formula that alansidman points to in response #3 in column H?

If you need help with that, ask alansidman.

Look at post #3. If prime then not composite. Formula shown will provide solution to that issue.

In the OP you asked for a formula (singular), but I don't know that this gets solved in one single formula.

In other words, construct all 1, 2, 3, 4, 5 and 6-digit numbers using the digits in A2:F2, and classify them as prime or not (= composite)?

Do you have an algorithm in mind? Here's what I see needing to happen (mostly outline -- no real programming, yet).

1) A permutation algorithm to combine the 6 digits into 1 to 6 digit numbers. I think most people prefer VBA or similar programming language for generating permutations because it can be done with a series of nested For...Next loops. I have a short tutorial here (https://www.excelforum.com/tips-and-...thout-vba.html ) for generating permutations that talks about both VBA nested loops and worksheet formula only approaches. Have a look and see if that helps with the "generate all the permutations" part of the problem.
2) Check if each number is prime or composite. The big question here is what kind of algorithm do you want to use. Do you want something long and laborious like trial division? Do you want to use a sieve (like the sieve of eratosthenes https://en.wikipedia.org/wiki/Sieve_of_Eratosthenes ) to check for primes? Often times, I think the easiest would be to find a list of prime numbers on the internet (here's a list in .csv format, if we trust the source to be accurate http://www.mathematical.com/primes0to1000k.html ), store that in the spreadsheet, and then run a simple lookup on that list to see if a number exists in that list.
3) You are asking for a text string "#composite/#prime" kind of output. After generating the permutations and determining which are prime and composite, we just need to count the number of each and concatenate together into the text string. As an Excel formula, this might look like =CONCATENATE(COUNTIFS(list,"composite"),"/",COUNTIFS(list,"prime")). I am assuming that steps 1 and 2 would generate a column/list of "composite" or "prime" values that COUNTIFS() can count.

Any thoughts on how you would like to proceed?

An alternative formula to achieve the same end result:

=IF(A3=2,"Prime",IF(AND(MOD(A3,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A3),0))))<>0),"Prime","Composite"))

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

working perfectly, thanks a lot Glenn Kennedy

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.