P
Peo Sjoblom
Or the VBA Mod which works better I believe
R
Ron Rosenfeld
=SUMPRODUCT(--(ODD(A1:A1000)=A1:A1000))
should work up to 999,999,999,999,999
--ron
R
Ron Rosenfeld
If you also want even number count, try:
=SUMPRODUCT((EVEN(A1:A10)=A1:A10)*(LEN(A1:A10)<>0))
--ron
R
Rick Rothstein \(MVP - VB\)
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
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
H
Harlan Grove
....Sandy Mann said:
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.
R
Ron Rosenfeld
If you use the ODD and EVEN functions, as I posted, you can handle up to 15
9's, I think.
--ron
R
Rick Rothstein \(MVP - VB\)
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
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
R
Ron Rosenfeld
OIC. Sometimes it's hard to keep track of who is talking about what in these
threads
)--ron
R
Rick Rothstein \(MVP - VB\)
That's true; I was just trying to tie up loose ends in the MOD function
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
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
R
Ron Rosenfeld
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