G
Guest
can anyone tell me a formula that will sum just the even numbers or the odd
numbers in a range?
numbers in a range?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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.
=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.
B
Bernd P
Hello,
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)
No array formula.
Regards,
Bernd
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)
No array formula.
Regards,
Bernd
R
Rick Rothstein \(MVP - VB\)
=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
That is for odd numbers... I guess you can use this for even numbers...
=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)
Rick
B
Bob Phillips
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)
=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)
G
Guest
Thank you all for your help. I dont really understand the MOD() formula but
all of your suggestions worked, with array and without array.
all of your suggestions worked, with array and without array.
R
Rick Rothstein \(MVP - VB\)
=SUMPRODUCT(MOD(A1:A6,2),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).
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
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
R
Rick Rothstein \(MVP - VB\)
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
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
D
David Biddulph
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.
help facility. It will explain, usually give examples, & often also
cross-refer to related functions.
B
Bob Phillips
Rick Rothstein (MVP - VB) said: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.
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.
R
Rick Rothstein \(MVP - VB\)
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)
Ahhhh... that "intuitive". <g>
Rick
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
T
T. Valko
The one that annoys me most is...WEEKNUM
For me, it's RANK (athough there are work-arounds).
Biff
For me, it's RANK (athough there are work-arounds).
Biff
B
Bob Phillips
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)
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.