Counting odd & even #s

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a simple way to count "odd" numbers in a column/row? And is there
any limit on the size of the actual number?

Thanx in advance...
 
Is there a simple way to count "odd" numbers in a column/row?

Odd: =SUMPRODUCT(--(MOD(MOD(A1:A11,1000),2)=1))

Even: =SUMPRODUCT(--(MOD(A1:A11,2)=0))
And is there any limit on the size of the actual number?
Click to expand...

Using the above, the largest testable number is 99999999999 (there are
eleven 9s in that number).

Rick
 
Is there a simple way to count "odd" numbers in a column/row?
Odd: =SUMPRODUCT(--(MOD(MOD(A1:A11,1000),2)=1))

Even: =SUMPRODUCT(--(MOD(A1:A11,2)=0))


Using the above, the largest testable number is 99999999999 (there are
eleven 9s in that number).
Click to expand...

Use Teethless mama's routine... it appears to be good for up to fifteen 9s.

Rick
 
With the numbers in A1:A100:

Odd Numbers:

=SUM(MOD(A1:A100,2))

Array entered with Ctrl + Shift + Enter or:

=SUMPRODUCT(MOD(A1:A100,2))

normally entered.

Even numbers:

=COUNT(A1:A100)-B1

normally entered

Where B1 is the cell holding the above odd number formula.

The largest number that can be stored in XL is: 9.9999999999999E+308

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Is there a simple way to count "odd" numbers in a column/row?
Use Teethless mama's routine... it appears to be good for up to fifteen
9s.
Click to expand...

Okay, you can still use Teethless mama's routine if you want, but this
modification to my formula makes it work for the same maximum size value...

Odd: =SUMPRODUCT(--(MOD(MOD(A1:A11,10000000),2)=1))

Even: =SUMPRODUCT(--(MOD(MOD(A1:A11,10000000),2)=0))

Rick
 
Rick,

Just a word of caution:
Even: =SUMPRODUCT(--(MOD(A1:A11,2)=0))
Click to expand...

Will count any empty cell as even.

However, I never thought about the limit on MOD() but I can only get eight
9's before MOD() error out in XL97

--
Regadrs,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
That limits the maximum testable limit to 99999999. My (eventual) formula
handles up to 999999999999999 like your formula does.

Rick
 
Will count any empty cell as even.

Good point... I forgot to test for that.
However, I never thought about the limit on MOD() but I can only
get eight 9's before MOD() error out in XL97
Click to expand...

Same limit in XL2003 which is why I added the second Mod function call; my
eventual final formula allows up to fifteen 9s (like Teethless mama's
formula) before crapping out.

Rick
 
Buggy with respect to counting the blank as zero or buggy in some other way?

Rick
 
Thanx to all who responded. And special thanx to Sandy Mann for figuring out
the shortest formulas.

Cheers,
Ed
 
Shortest is good but its not always best. As Rick pointed out MOD() is
limited but it will do for my pension <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanx to all who responded. And special thanx to Sandy Mann for figuring
out
the shortest formulas.
Click to expand...

Well, at least the shortest formula for the smallest maximum possible number
at least. When you asked "And is there any limit on the size of the actual
number?", I thought size was a concern of yours, so I posted a formula
deliberately constructed to maximize the number that could be handled.
Teethless mama's maximum number handling was inherent due to the method he
used to construct his formula. Anyway, I'm glad that at least one of our
replies satisfied your requirements.

Rick
 
Another possible way:

For Odd.
=SUMPRODUCT(--(NOT((B1:B4)/2=INT(B1:B4/2))))

For Even.
=SUMPRODUCT(--((B1:B4)/2=INT(B1:B4/2)))

Regards,
Paul
 
Thanks for the link. I am not sure the problems discussed there would affect
the usage that I put MOD to for this thread (at least I hope it wouldn't),
but that is definitely something to keep in mind for future possible uses of
this function. Perhaps going to the mathematical equivalent calculations for
MOD would be something to do in the future.

Rick
 
Teethless mama said:
Counting odd number

=SUMPRODUCT(--(--RIGHT(A1:A100,1)={1,3,5,7,9}))
Click to expand...
....

Only integers could be odd or even. Your formula would count
nonintegers with last decimal place an odd digit.
 
For Even.
=SUMPRODUCT(--((B1:B4)/2=INT(B1:B4/2)))
Click to expand...

This counts empty cells as even

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top