Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help in Excel 2000
How do I get sum of all odd or even numbers from the series (1, 2, 3, .....,
n) by using SUMIF function and MOD function or any other function? |
#2
|
|||
|
|||
One way:
Assuming series / numbers is in row1 Sum of Even: =SUMPRODUCT(--(MOD((1:1),2)=0),1:1) Sum of Odd: =SUMPRODUCT(--(MOD((1:1),2)=1),1:1) If there could be possibly some "text" numbers in row1, try: Sum of Even: =SUMPRODUCT(--(MOD((1:1),2)=0),--(1:1)) Sum of Odd: =SUMPRODUCT(--(MOD((1:1),2)=1),--(1:1)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Akhilesh Dalia <Akhilesh wrote in message ... How do I get sum of all odd or even numbers from the series (1, 2, 3, ......, n) by using SUMIF function and MOD function or any other function? |
#3
|
|||
|
|||
Note that all the suggested formulas are reading the entire row1
hence these formulas have to be entered in cells *other* than those in row1, otherwise you'll get circular ref errors ! If you need to enter the formulas in the same row, use range references (e.g.: A1:F1), i.e. something like: In G1: =SUMPRODUCT(--(MOD((A1:F1),2)=0),A1:F1) Then G1 can be copied down to evaluate similarly for other series in A2:F2, A3:F3, etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Thank you very much!!
I got my job done. You provided a great help. Thanks once again!! Regards Akhilesh Dalia "Max" wrote: Note that all the suggested formulas are reading the entire row1 hence these formulas have to be entered in cells *other* than those in row1, otherwise you'll get circular ref errors ! If you need to enter the formulas in the same row, use range references (e.g.: A1:F1), i.e. something like: In G1: =SUMPRODUCT(--(MOD((A1:F1),2)=0),A1:F1) Then G1 can be copied down to evaluate similarly for other series in A2:F2, A3:F3, etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
Glad to hear it helped !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Akhilesh Dalia <Akhilesh wrote in message ... Thank you very much!! I got my job done. You provided a great help. Thanks once again!! Regards Akhilesh Dalia |
#6
|
|||
|
|||
"Max" wrote...
One way: Assuming series / numbers is in row1 Sum of Even: =SUMPRODUCT(--(MOD((1:1),2)=0),1:1) Sum of Odd: =SUMPRODUCT(--(MOD((1:1),2)=1),1:1) .... The original question looked like it could have been solved using standard variations on Gauss's formula. Sum of odd numbers from 1 to N = ((N + 1)/2)^2 for N odd. Sum of even numbers from 2 to N = N * (N + 2)/4 for N even. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) | |||
pivotcell object to excel 2000 | Excel Discussion (Misc queries) | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |