Counting odd & even #s

  • Thread starter Thread starter Guest
  • Start date Start date
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...


=SUMPRODUCT(--(ODD(A1:A1000)=A1:A1000))

should work up to 999,999,999,999,999


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

If you also want even number count, try:

=SUMPRODUCT((EVEN(A1:A10)=A1:A10)*(LEN(A1:A10)<>0))


--ron
 
Actually, the mathematical version of summing odd numbers is reasonably
simply, either...

=SUMPRODUCT(--(A1:A20-2*INT(A1:A20/2)=1))

normal entered or

=SUM(--(A1:A20-2*INT(A1:A20/2)=1))

array entered. However, there is a limitation to this version... you can
only handle numbers to a maximum value of fourteen (not fifteen) 9s. The
even numbers versions would be...

=SUMPRODUCT((A1:A20-2*INT(A1:A20/2)=0)*(A1:A20<>""))

normally entered and

=SUM((A1:A20-2*INT(A1:A20/2)=0)*(A1:A20<>""))

array entered.

Rick
 
Sandy Mann said:
This counts empty cells as even
....

If you want to include only integers in the odd and even counts,

even:
=SUMPRODUCT(ISNUMBER(rng)*(rng=2*INT(rng/2)))

odd:
=SUMPRODUCT(ISNUMBER(rng)*(rng-1=2*INT(rng/2)))

Your =SUMPRODUCT(MOD(rng,2)) formula would include noninteger numbers
in the result along with returning errors if any values in rng were
greater than or equal to 268,435,456.
 
Actually, the mathematical version of summing odd numbers is reasonably
simply, either...

=SUMPRODUCT(--(A1:A20-2*INT(A1:A20/2)=1))

normal entered or

=SUM(--(A1:A20-2*INT(A1:A20/2)=1))

array entered. However, there is a limitation to this version... you can
only handle numbers to a maximum value of fourteen (not fifteen) 9s. The
even numbers versions would be...

=SUMPRODUCT((A1:A20-2*INT(A1:A20/2)=0)*(A1:A20<>""))

normally entered and

=SUM((A1:A20-2*INT(A1:A20/2)=0)*(A1:A20<>""))

array entered.

If you use the ODD and EVEN functions, as I posted, you can handle up to 15
9's, I think.
--ron
 
That's true; I was just trying to tie up loose ends in the MOD function
discussion. The generic mathematical equivalent for the MOD function is
this...

Remainder = Number - Modulus * Int(Number / Modulus)

where, for this discussion, the Modulus is 2... this is where my last
formulas came from.

Rick
 
That's true; I was just trying to tie up loose ends in the MOD function
discussion. The generic mathematical equivalent for the MOD function is
this...

Remainder = Number - Modulus * Int(Number / Modulus)

where, for this discussion, the Modulus is 2... this is where my last
formulas came from.

Rick

OIC. Sometimes it's hard to keep track of who is talking about what in these
threads :-))
--ron
 
That's true; I was just trying to tie up loose ends in the MOD function
OIC. Sometimes it's hard to keep track of who is talking about what
in these threads :-))

Yeah, this thread has gotten kind of long, so I know what you mean. Still,
things aren't too bad here as there is a tree structure to help out. In
addition to VB, I also fool around with a program called REALbasic
(cross-platform visual Basic type language) and, back when I first obtained
the program, their peer-support was through "mail lists" rather than
newsgroups. I signed up for several of these mail lists and my Inbox was
inundated with what seemed to me to be a completely unstructured
question/response pool. I couldn't figure out who asked what, what answers
applied to what questions, what questions had been resolved and so on. I
simply could not follow what was going on and so I dropped out of all the
mail lists I had subscribed to after a couple of weeks (the mail lists
eventually gave way to forum type participation). So, from my point of view,
it just cannot get too convoluted here for me to track... it's like heaven
here compared to those mail lists.<g>

Rick
 
Yeah, this thread has gotten kind of long, so I know what you mean. Still,
things aren't too bad here as there is a tree structure to help out. In
addition to VB, I also fool around with a program called REALbasic
(cross-platform visual Basic type language) and, back when I first obtained
the program, their peer-support was through "mail lists" rather than
newsgroups. I signed up for several of these mail lists and my Inbox was
inundated with what seemed to me to be a completely unstructured
question/response pool. I couldn't figure out who asked what, what answers
applied to what questions, what questions had been resolved and so on. I
simply could not follow what was going on and so I dropped out of all the
mail lists I had subscribed to after a couple of weeks (the mail lists
eventually gave way to forum type participation). So, from my point of view,
it just cannot get too convoluted here for me to track... it's like heaven
here compared to those mail lists.<g>

Rick

There is a mail list type group to which I subscribe. It has to do with a
piece of aviation electronics. The default, which is done by most posters, is
to quote everything that has ever been written in that thread, and to top post
your response. Not only is it difficult to ascertain which point the poster
might be responding to, but the format makes it difficult to tell where one
post ends and the next one starts!
--ron
 
Back
Top