#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default sum odd numbers

can anyone tell me a formula that will sum just the even numbers or the odd
numbers in a range?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default sum odd numbers

Even:

=SUM(IF(MOD($A$1:$A$10,2)=0,$A$1:$A$10))

Odd:

=SUM(IF(MOD($A$1:$A$10,2)=1,$A$1:$A$10))

ENTER both with Ctrl+Shift+Enter

You will get {} round the formula if entered correctly.

"twty" wrote:

can anyone tell me a formula that will sum just the even numbers or the odd
numbers in a range?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 806
Default sum odd numbers

Hello,

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

No array formula.

Regards,
Bernd

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default sum odd numbers

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

That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Rick
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default sum odd numbers

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)

Pity about ISEVEN

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)


That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Rick





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default sum odd numbers

Thank you all for your help. I dont really understand the MOD() formula but
all of your suggestions worked, with array and without array.

"Bob Phillips" wrote:

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)

Pity about ISEVEN

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)


That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Rick




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default sum odd numbers

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

That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)


More intuitive? Well, marginally (at least for me). Since I had no trouble
seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed cell is odd and
0 when it is even, I find no difficulty seeing that subtracting these values
(0 or 1) from 1 reverses the values (they become 1 or 0 respectively) and,
hence, their odd/even-ness (it is nothing more than the principal of
toggling a value between 0 and 1 inside a program where the code line would
be Value=1-Value).

Pity about ISEVEN


I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array ranges
in this way and others (like ISEVEN) can't? Is there some "rule" governing
which function can and cannot?

Rick

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default sum odd numbers

Thank you all for your help. I dont really understand the MOD() formula

The MOD function returns the remainder after division. So, for this...

=MOD(17,5)

look at the division 17/5, the answer is 3 with a remainder of 2. The above
formula returns the remainder, in this case, 2. Now, for odd numbers, when
divided by 2, they always have a remainder of 1. So, the expression
MOD(SomeNumber,2) will evaluate to 1 whenever SomeNumber is odd. Also, an
even number always has a remainder of 0 (2 always divides even numbers
evenly, sort of by definition). So MOD(SomeNumber,2) will evaluate to 0
whenever SomeNumber is even. The SUMPRODUCT formula that Bernd posted makes
use of this to multiply the odd numbers by their return value of 1 and the
even numbers by their return value of 0, thus resulting in the addition of
only the odd numbers.

Rick

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default sum odd numbers

For functions that you don't understand, I recommend that you use Excel's
help facility. It will explain, usually give examples, & often also
cross-refer to related functions.
--
David Biddulph

"twty" wrote in message
...
Thank you all for your help. I dont really understand the MOD() formula
but
all of your suggestions worked, with array and without array.



  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default sum odd numbers


"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)

That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)


More intuitive? Well, marginally (at least for me). Since I had no trouble
seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed cell is odd
and 0 when it is even, I find no difficulty seeing that subtracting these
values (0 or 1) from 1 reverses the values (they become 1 or 0
respectively) and, hence, their odd/even-ness (it is nothing more than the
principal of toggling a value between 0 and 1 inside a program where the
code line would be Value=1-Value).


But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.



Pity about ISEVEN


I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array
ranges in this way and others (like ISEVEN) can't? Is there some "rule"
governing which function can and cannot?



I don't know for sure, but I guess it is just because that Excel is such a
big program that different parts were developed by different teams. One team
developed all of their functions to return arrays, one didn't. The one that
annoys me most is WEEKDAY/WEEKNUM. WEEKDAY does, WEEKNUM doesn't.




  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default sum odd numbers

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

That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)


More intuitive? Well, marginally (at least for me). Since I had no
trouble seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed cell
is odd and 0 when it is even, I find no difficulty seeing that
subtracting these values (0 or 1) from 1 reverses the values (they become
1 or 0 respectively) and, hence, their odd/even-ness (it is nothing more
than the principal of toggling a value between 0 and 1 inside a program
where the code line would be Value=1-Value).


But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.


Ahhhh... that "intuitive". <g

Rick

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default sum odd numbers

The one that annoys me most is...WEEKNUM

For me, it's RANK (athough there are work-arounds).

Biff

"Bob Phillips" wrote in message
...

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)

That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)


More intuitive? Well, marginally (at least for me). Since I had no
trouble seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed cell
is odd and 0 when it is even, I find no difficulty seeing that
subtracting these values (0 or 1) from 1 reverses the values (they become
1 or 0 respectively) and, hence, their odd/even-ness (it is nothing more
than the principal of toggling a value between 0 and 1 inside a program
where the code line would be Value=1-Value).


But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.



Pity about ISEVEN


I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array
ranges in this way and others (like ISEVEN) can't? Is there some "rule"
governing which function can and cannot?



I don't know for sure, but I guess it is just because that Excel is such a
big program that different parts were developed by different teams. One
team developed all of their functions to return arrays, one didn't. The
one that annoys me most is WEEKDAY/WEEKNUM. WEEKDAY does, WEEKNUM doesn't.



  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default sum odd numbers

There are with them all Biff, but it would be nice if ...

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"T. Valko" wrote in message
...
The one that annoys me most is...WEEKNUM


For me, it's RANK (athough there are work-arounds).

Biff

"Bob Phillips" wrote in message
...

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)

That is for odd numbers... I guess you can use this for even
numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)

More intuitive? Well, marginally (at least for me). Since I had no
trouble seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed
cell is odd and 0 when it is even, I find no difficulty seeing that
subtracting these values (0 or 1) from 1 reverses the values (they
become 1 or 0 respectively) and, hence, their odd/even-ness (it is
nothing more than the principal of toggling a value between 0 and 1
inside a program where the code line would be Value=1-Value).


But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.



Pity about ISEVEN

I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array
ranges in this way and others (like ISEVEN) can't? Is there some "rule"
governing which function can and cannot?



I don't know for sure, but I guess it is just because that Excel is such
a big program that different parts were developed by different teams. One
team developed all of their functions to return arrays, one didn't. The
one that annoys me most is WEEKDAY/WEEKNUM. WEEKDAY does, WEEKNUM
doesn't.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Decimal Numbers typed into Excel 2003 read as whole numbers john mcmichael Excel Discussion (Misc queries) 1 May 10th 07 08:18 PM
Excel 2002 : Convert Positive Numbers to Negative Numbers ? Mr. Low Excel Discussion (Misc queries) 2 November 6th 06 04:30 PM
Excel Formula - Add column of numbers but ignore negative numbers view for Distribution List members Excel Worksheet Functions 1 April 7th 06 03:13 AM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 3 January 19th 06 10:52 AM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 1 January 9th 06 02:23 PM


All times are GMT +1. The time now is 07:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"