Sumproduct returns wrong result

[sadath]

following is the part of formula which returns wrong result...

SUMPRODUCT(IF(MOD(ROW(1:1),2)=0,-1,1)) result is 1 which is correct

SUMPRODUCT(IF(MOD(ROW(2:2),2)=0,-1,1)) result is -1 which is correct

SUMPRODUCT(IF(MOD(ROW(1:2),2)=0,-1,1)) result is 1, should be zero


why returns wrong result... any idea?

 

[Gerald Higgins]

Why do you think the result should be zero ?

And why are you using SUMPRODUCT ?
Deleting the SUMPRODUCT element gives the same results.

 

[steve the fish]

You need CSE. It will produce 0 then. Sum of {1;-1}

 

[sadath]

Hi
no idea why this returns wrong

=SUMPRODUCT(C1:C10*IF(MOD(ROW(1:10),2)=0,-1,1))

but this CSE works fine =SUM(C1:C100*IF(MOD(ROW(1:100),2)=0,-1,1)) thanks to 'steve the fish '

 

[steve the fish]

Looks like there should be an easier way to produce the sequence but this works without CSE:

=SUMPRODUCT((MOD(ROW(C1:C10),2)=0)*(MOD(ROW(C1:C10),2)-2)+1,C1:C10)

 

[Jonmo1]

In short

SUMPRODUCT(IF(MOD(ROW(1:2),2)=0,-1,1))

Without CTRL + SHIFT + ENTER
ROW(1:2) simply returns 1, so you have
SUMPRODUCT(IF(MOD(1,2)=0,-1,1))

WITH CTRL + SHIFT + ENTER
ROW(1:2) will return the array {1,2}
So you get
SUMPRODUCT(IF(MOD({1,2},2)=0,-1,1))


Hope that helps.

 

[barry houdini]

Looks like there should be an easier way to produce the sequence....

How about......

=SUMPRODUCT(C1:C10,MOD(ROW(C1:C10),2)*2-1)