| 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 |
| 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 | |||
Hybrid Mode
