Counting odd & even #s

[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...

 

[Guest]

Counting odd number

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

 

[Rick Rothstein \(MVP - VB\)]

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?

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Sandy Mann]

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

 

[Rick Rothstein \(MVP - VB\)]

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.

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

 

[Guest]

Short version for "odd" numbers

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

 

[Sandy Mann]

Rick,

Just a word of caution:

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

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

 

[Rick Rothstein \(MVP - VB\)]

That limits the maximum testable limit to 99999999. My (eventual) formula
handles up to 999999999999999 like your formula does.

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peo Sjoblom]

MOD is known to be a buggy function

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Guest]

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

Cheers,
Ed

 

[Sandy Mann]

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

 

[Peo Sjoblom]

In some other way

http://tinyurl.com/27cqt2

 

[Rick Rothstein \(MVP - VB\)]

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

 

[PCLIVE]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Harlan Grove]

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.

 

[Sandy Mann]

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

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