Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum odd numbers
can anyone tell me a formula that will sum just the even numbers or the odd
numbers in a range? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum odd numbers
Hello,
=SUMPRODUCT(MOD(A1:A6,2),A1:A6) No array formula. Regards, Bernd |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Numbers typed into Excel 2003 read as whole numbers | Excel Discussion (Misc queries) | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
Excel Formula - Add column of numbers but ignore negative numbers | Excel Worksheet Functions | |||
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? | Excel Worksheet Functions | |||
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? | Excel Worksheet Functions |