Counting odd & even #s

MOD is known to be a buggy function



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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


"Peo Sjoblom" wrote in message
...
MOD is known to be a buggy function



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

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

Cheers,
Ed

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Ed" wrote in message
...
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...

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


Replace @mailinator.com with @tiscali.co.uk


"Ed" wrote in message
...
Thanx to all who responded. And special thanx to Sandy Mann for figuring
out
the shortest formulas.

Cheers,
Ed

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Ed" wrote in message
...
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...

In some other way

http://tinyurl.com/27cqt2




--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
Buggy with respect to counting the blank as zero or buggy in some other
way?

Rick


"Peo Sjoblom" wrote in message
...
MOD is known to be a buggy function



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

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

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


--

"Ed" wrote in message
...
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...

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


"Peo Sjoblom" wrote in message
...
In some other way

http://tinyurl.com/27cqt2




--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
Buggy with respect to counting the blank as zero or buggy in some other
way?

Rick


"Peo Sjoblom" wrote in message
...
MOD is known to be a buggy function



--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

Teethless mama wrote...
Counting odd number

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

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)))

This counts empty cells as even

--
Regards,

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


Replace @mailinator.com with @tiscali.co.uk


"PCLIVE" wrote in message
...
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


--

"Ed" wrote in message
...
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...