Prime and Composite Numbers

[Billiebones]

Hi all,

I have a spreadsheet see attached, where I have numbers in columns C to H. In columns K to P I have a formula that tells me if the numbers in columns C to H are prime Numbers or Composite Numbers. The problem I have is, for most of the cells the formula is correct for prime and composite, but a few cells have composite when it should have prime or prime when it should be composite.

The array formula I'm using is:

=IF(C2=2,"Prime",IF(OR(MOD(C2,{2,3,5,7,9})=0),"Composite","Prime"))

[AliGW]

Do us a favour, please, and indicate the cells we need to look at with a yellow fill. Thanks.

[Billiebones]

Hi Ali the cells you need to look at are K to P

[AliGW]

Which specific cells in K and P?

[Billiebones]

I have uploaded a new copy with the problem highlighted yellow

[AliGW]

Try this:

=IF(C2=2,"Prime",IF(MOD(C2,{2,3,5,7,9})=0,"Composite","Prime"))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[MrShorty]

A couple that I have seen are K10 and K12, where you are testing if 7 is prime by testing if 7 mod 7 is 0 -- which it is. Your test decides that 7 is composite because 7 is divisible by itself. Your algorithm is incomplete and that's why it fails.

This looks like something for lottery analysis, so you are limited to the numbers 1 to 45 or so? If I were doing this, rather than try to build a robust prime number sieve (search "sieve of eratosthenes" if you want to see how the ancient Greeks figured out how to do it), I would probably just make a list of all the prime numbers between 1 and 45, then use a formula that checks if my number is in the list.

[AliGW]

I think you might have missed post #6.

[Billiebones]

Thanks Ali it did not work, although it fixed the first problem I highlighted for you, it has now changed others, if you look at row 144 you will see I've highlighted number 25 as showing prime, however it's a composite number.

[Billiebones]

I don't understand what MrShorty is saying, I have reach my limits of knowledge on excel. Can someone please correct my formula to make it work properly please.

[AliGW]

That's what we are trying to do.

[Billiebones]

Ok thank you

[AliGW]

The problem with the array is that it's not working. I am looking for an alternative.

[Billiebones]

Thanks Ali I appreciate your help

[Pepe Le Mokko]

Try =IF(OR(C2=1;C2=2;C2=3);"prime";IF(AND((MOD(C2;ROW(INDIRECT("2:"&C2-1)))<>0));"prime";"Composite")) and pull down and right as required (replace semi colons with commas if needed)
in K2 entered as an array formula

[AliGW]

Very odd - sometimes your formula is working for 2, sometimes not.

[Billiebones]

Hi Pepe Le Mokko, that worked thank you I did have to replace the semi Colin with commas. thanks so much for your help and thanks to all for your replies and help.

[AliGW]

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

[Rick Rothstein]

Try =IF(OR(C2=1;C2=2;C2=3);"prime";IF(AND((MOD(C2;ROW(INDIRECT("2:"&C2-1)))<>0));"prime";"Composite"))

Why not replace what I highlighted in red above with the following?

C2<4

[Pepe Le Mokko]

I forgot to mention that the solution I proposed was found on the site of the late C Pearson.
Thank you Chuck !

[AliGW]

Chuck? Do you mean Chip?