I tried the formulare
It seems that it works only every two lines. It doesn't work when I change the every line to 3, 4, 5 and etc.= sumproduct(--(mod(a1:a30,2)=1),a1:a30).
Thanks!
I tried the formulare
It seems that it works only every two lines. It doesn't work when I change the every line to 3, 4, 5 and etc.= sumproduct(--(mod(a1:a30,2)=1),a1:a30).
Thanks!
Did you useOriginally Posted by Jane
etc.=sumproduct(--(mod(a1:a30,3)=1),a1:a30) =sumproduct(--(mod(a1:a30,4)=1),a1:a30) =sumproduct(--(mod(a1:a30,5)=1),a1:a30)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hello, Xld:
Yes, I tried the following ways. Did I do something wrong?
etc.=sumproduct(--(mod(a1:a30,3)=1),a1:a30) =sumproduct(--(mod(a1:a30,4)=1),a1:a30) =sumproduct(--(mod(a1:a30,5)=1),a1:a30)
Thank you very much!
Hi Jane,Originally Posted by Jane
No that is what I did.
If you put 1,2,3,4,5,6,7,8,9,10 in a1:a10, what do you get for each of those 3 formulae?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi, Xld:
I attached a spreadsheet for your review. I don't know which step I did is wrong.
Thank you for your help.
Jane
Hi Jane,Originally Posted by Jane
This is going to be tricky to explain, so bear with me.
The original formula is
which sums every other row, starting at the first ODD row in the range. Note here that the first row in the range is an odd numbered row.= sumproduct(--(mod(row(a1:a30),2)=1),a1:a30).
You have shifted the range to C8:C18, note that the first row is now EVEN numbered row. So the formula
does not start at the first row in that range, but the first odd row. And it gets more complex with the other MODs.=SUMPRODUCT(--(MOD(ROW($C$8:$C$18),3)=0),$C$8:$C$18)
Solution?
You need to set the start point relative to the first row in the range
But don't even ask what happens if you want the first row to be the second in the range!=SUMPRODUCT(--(MOD(ROW($C$8:$C$18),3)=MOD(ROW($C$8),3)),$C$8:$C$18) =SUMPRODUCT(--(MOD(ROW($C$8:$C$18),4)=MOD(ROW($C$8),4)),$C$8:$C$18) =SUMPRODUCT(--(MOD(ROW($C$8:$C$18),5)=MOD(ROW($C$8),5)),$C$8:$C$18)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi, Xld:
Thank you very much! It works now.
Jane