Excel 97 - Adding Every 8th Row - Formula should work, but doesn't
 

Here's a formula I found online to add every 7th row:

=SUMPRODUCT(--(MOD(ROW(A1:A300)-1,7)=0),A1:A300)

I've modified it, of course, to fit the cells I'm working with and I
changed it to add every eighth row. (I'm actually working with cells T12 -
T428, but I made the cell range smaller for debugging purposes.)

=SUMPRODUCT(--(MOD(ROW(T412:T428)-1,8)=0),T412:T428)

It SHOULD start with T412 and add it to T420 and T428, but for some strange
reason, it adds T417 and T425 only and returns the result. Can anybody
understand why?



Now when I try the following:

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

It adds every other row just as it should (Sunday, Tuesday, Thursday,
Saturday)



This adds every third row (Sunday, Wednesday, Saturday)

=SUMPRODUCT(--(MOD(ROW(T421:T427)-1,3)=0),T421:T427)


So why does that formula work in the last two examples, but not in the
first? What I have on the spreadsheet is a row for every day of the week,
followed by a weekly total row. Then the formula should go through the
entire year of 2004 and add up the totals of each week, which is every
eighth row. The modified formula at the top SHOULD do this for the last
three weeks, but it doesn't. The totals for the last three weeks are 426,
269, and 365. But the formula returns a value of 102. That value is the
total of cells T417 (43) and T425 (59).

Why is this happening?

Thanks,
Damaeus

Try the following formula...

=SUMPRODUCT(--(MOD(ROW(T12:T428)-ROW(T12),8)=0),T12:T428)

Notice I changed the number 1 in the formula to ROW(T12). If this part
refers to the first cell in the range, then in future all you have to do
is change the MOD operator (in this case the number 8) accordingly.

Hope this helps!

In article ,
Damaeus wrote:

Here's a formula I found online to add every 7th row:

=SUMPRODUCT(--(MOD(ROW(A1:A300)-1,7)=0),A1:A300)

I've modified it, of course, to fit the cells I'm working with and I
changed it to add every eighth row. (I'm actually working with cells T12 -
T428, but I made the cell range smaller for debugging purposes.)

=SUMPRODUCT(--(MOD(ROW(T412:T428)-1,8)=0),T412:T428)

It SHOULD start with T412 and add it to T420 and T428, but for some strange
reason, it adds T417 and T425 only and returns the result. Can anybody
understand why?



Now when I try the following:

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

It adds every other row just as it should (Sunday, Tuesday, Thursday,
Saturday)



This adds every third row (Sunday, Wednesday, Saturday)

=SUMPRODUCT(--(MOD(ROW(T421:T427)-1,3)=0),T421:T427)


So why does that formula work in the last two examples, but not in the
first? What I have on the spreadsheet is a row for every day of the week,
followed by a weekly total row. Then the formula should go through the
entire year of 2004 and add up the totals of each week, which is every
eighth row. The modified formula at the top SHOULD do this for the last
three weeks, but it doesn't. The totals for the last three weeks are 426,
269, and 365. But the formula returns a value of 102. That value is the
total of cells T417 (43) and T425 (59).

Why is this happening?

Thanks,
Damaeus

Try...

=SUMPRODUCT(--(MOD(ROW(T412:T428)-CELL("Row",T412)+0,8)=0),T412:T428)

if summing must start with the first cell of the range.

=SUMPRODUCT(--(MOD(ROW(T412:T428)-CELL("Row",T412)+1,8)=0),T412:T428)

if summing must start with the first occurrence of the Nth (8th).

Damaeus wrote:
Here's a formula I found online to add every 7th row:

=SUMPRODUCT(--(MOD(ROW(A1:A300)-1,7)=0),A1:A300)

I've modified it, of course, to fit the cells I'm working with and I
changed it to add every eighth row. (I'm actually working with cells T12 -
T428, but I made the cell range smaller for debugging purposes.)

=SUMPRODUCT(--(MOD(ROW(T412:T428)-1,8)=0),T412:T428)

It SHOULD start with T412 and add it to T420 and T428, but for some strange
reason, it adds T417 and T425 only and returns the result. Can anybody
understand why?



Now when I try the following:

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

It adds every other row just as it should (Sunday, Tuesday, Thursday,
Saturday)



This adds every third row (Sunday, Wednesday, Saturday)

=SUMPRODUCT(--(MOD(ROW(T421:T427)-1,3)=0),T421:T427)


So why does that formula work in the last two examples, but not in the
first? What I have on the spreadsheet is a row for every day of the week,
followed by a weekly total row. Then the formula should go through the
entire year of 2004 and add up the totals of each week, which is every
eighth row. The modified formula at the top SHOULD do this for the last
three weeks, but it doesn't. The totals for the last three weeks are 426,
269, and 365. But the formula returns a value of 102. That value is the
total of cells T417 (43) and T425 (59).

Why is this happening?

Thanks,
Damaeus

Hi Aladin!

I do like your formula. I see you use the CELL function instead of ROW,
as I have. I certainly find it looks better and plan to steal...excuse
me...adopt it. :) But I have two questions...

1) Other than the way it looks, is there an advantage in using the CELL
function instead of ROW?

2) Why do you include '+0' when there doesn't seem a need for coercion?

In article ,
Aladin Akyurek wrote:

Try...

=SUMPRODUCT(--(MOD(ROW(T412:T428)-CELL("Row",T412)+0,8)=0),T412:T428)

if summing must start with the first cell of the range.

=SUMPRODUCT(--(MOD(ROW(T412:T428)-CELL("Row",T412)+1,8)=0),T412:T428)

if summing must start with the first occurrence of the Nth (8th).

Domenic wrote:
Hi Aladin!

I do like your formula. I see you use the CELL function instead of ROW,
as I have. I certainly find it looks better and plan to steal...excuse
me...adopt it. :) But I have two questions...

1) Other than the way it looks, is there an advantage in using the CELL
function instead of ROW?

ROW() always returns an array, not a scalar. Hence the choice.


2) Why do you include '+0' when there doesn't seem a need for coercion?


I devised this formula many moons ago to capture multiple situations in
which the 'every Nth' questions arise (see SUMEVERY.XLS, I believe, by
Pearson). One set is whether one wants the formula to operate starting
with the topmost cell or the first Nth cell.

+0 means: start with the topmost cell (not coercion) and +1 start with
the first Nth.

In article ,
Aladin Akyurek wrote:


Try...

=SUMPRODUCT(--(MOD(ROW(T412:T428)-CELL("Row",T412)+0,8)=0),T412:T428)

if summing must start with the first cell of the range.

=SUMPRODUCT(--(MOD(ROW(T412:T428)-CELL("Row",T412)+1,8)=0),T412:T428)

if summing must start with the first occurrence of the Nth (8th).

In article ,
Aladin Akyurek wrote:

ROW() always returns an array, not a scalar. Hence the choice.

Even with the following reference?

ROW(T412)

Doesn't that return a single number, that being 412?

I devised this formula many moons ago to capture multiple situations in
which the 'every Nth' questions arise (see SUMEVERY.XLS, I believe, by
Pearson). One set is whether one wants the formula to operate starting
with the topmost cell or the first Nth cell.

+0 means: start with the topmost cell (not coercion) and +1 start with
the first Nth.

I definitely like it. I'll be adopting it, as I usually try to do with
any of your formulas. :)

Apply F9. It should be: {412}.

Domenic wrote:
In article ,
Aladin Akyurek wrote:


ROW() always returns an array, not a scalar. Hence the choice.


Even with the following reference?

ROW(T412)

Doesn't that return a single number, that being 412?


I devised this formula many moons ago to capture multiple situations in
which the 'every Nth' questions arise (see SUMEVERY.XLS, I believe, by
Pearson). One set is whether one wants the formula to operate starting
with the topmost cell or the first Nth cell.

+0 means: start with the topmost cell (not coercion) and +1 start with
the first Nth.


I definitely like it. I'll be adopting it, as I usually try to do with
any of your formulas. :)

In article ,
Aladin Akyurek wrote:

Apply F9. It should be: {412}.

Oh I see. Even though it returns a single number, it returns it as a
'one cell array'. Now I understand why you opt for the CELL function.

By the way, is there an F9 equivalent for the Mac version of Excel? F9
doesn't work for me.

Domenic wrote:
In article ,
Aladin Akyurek wrote:


Apply F9. It should be: {412}.


Oh I see. Even though it returns a single number, it returns it as a
'one cell array'. Now I understand why you opt for the CELL function.

By the way, is there an F9 equivalent for the Mac version of Excel? F9
doesn't work for me.

It's cmd+= n my Excel Mac 2001.

In article ,
Aladin Akyurek wrote:

It's cmd+= n my Excel Mac 2001.

Well, it looks like I'm out of luck. It doesn't work in my Excel X for
Mac. That's the 'Calculate Now' command when in manual calculation mode.

But thanks for the earlier 'class session'! :) Much appreciated!

Cheers!

The problem you're having is that you don't understand what the formula is
doing, and therefore , you can't revise it to work for your particular
situation. It's not exactly an easy one to follow.

Either of these will work for your scenario:

=SUMPRODUCT((MOD(ROW(T12:T428)+4,8)=0)*(T12:T428))

=SUMPRODUCT((MOD(ROW(T12:T428)-4,8)=0)*(T12:T428))

Or even:

=SUMPRODUCT((MOD(ROW(T12:T428)-12,8)=0)*(T12:T428))

The key to understanding this formula is to realize the relationship between
the starting cell of the range, the first cell you wish to start adding, and
the number of rows to cycle the actual calculation.

Those 3 numbers, as the MOD() function equates to in the formula, *must*
return a zero (a number, divided by a number evenly, with *no* remainder)!

At the start of your post , you stated:

<"Here's a formula I found online to add every 7th row:

=SUMPRODUCT(--(MOD(ROW(A1:A300)-1,7)=0),A1:A300) "

Here A1 = 1, but the " -1 " makes it "0", so "0" divided by 7 equals "0",
with a "0" remainder
So the calculations start at the beginning, "A1", and continue every 7 rows,
where ROW 8 minus 1 equals 7, which divided by 7 equals "0" remainder.

Now, follow this to the formula that you revised.

=SUMPRODUCT(--(MOD(ROW(T412:T428)-1,8)=0),T412:T428)

You start at T412, then minus 1 equals 411, which when divided by 8, does
*not* leave a "0" remainder, so it's "bypassed".
We now come down to where 417 minus 1 equals 416, which when divided by 8
leaves a "0" remainder, and as such, is calculated (added).

That tells you why your formula started at T417, and then continued every 8
rows.

You should now be able to understand why all of the 3 formulas I entered
above will work for your situation.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Damaeus" wrote in message
...
Here's a formula I found online to add every 7th row:

=SUMPRODUCT(--(MOD(ROW(A1:A300)-1,7)=0),A1:A300)

I've modified it, of course, to fit the cells I'm working with and I
changed it to add every eighth row. (I'm actually working with cells T12 -
T428, but I made the cell range smaller for debugging purposes.)

=SUMPRODUCT(--(MOD(ROW(T412:T428)-1,8)=0),T412:T428)

It SHOULD start with T412 and add it to T420 and T428, but for some strange
reason, it adds T417 and T425 only and returns the result. Can anybody
understand why?



Now when I try the following:

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

It adds every other row just as it should (Sunday, Tuesday, Thursday,
Saturday)



This adds every third row (Sunday, Wednesday, Saturday)

=SUMPRODUCT(--(MOD(ROW(T421:T427)-1,3)=0),T421:T427)


So why does that formula work in the last two examples, but not in the
first? What I have on the spreadsheet is a row for every day of the week,
followed by a weekly total row. Then the formula should go through the
entire year of 2004 and add up the totals of each week, which is every
eighth row. The modified formula at the top SHOULD do this for the last
three weeks, but it doesn't. The totals for the last three weeks are 426,
269, and 365. But the formula returns a value of 102. That value is the
total of cells T417 (43) and T425 (59).

Why is this happening?

Thanks,
Damaeus

In news:microsoft.public.excel.worksheet.functions, "RagDyeR"
posted on Sat, 22 Jan 2005 13:21:14 -0800:

You should now be able to understand why all of the 3 formulas I entered
above will work for your situation.

Jeez. Seems like it would make more sense to have a "step" command in
Excel.

=SUM(T12:T428(STEP(8))

Or something like that. :-)

Thanks for the information. I will study it carefully.

Damaeus

Take a look at Domenic's first solution. It is a generic version of
RD's. Use RD's multiplication technique and there is no need for the
double-negation. To me it seems that there's a subset of people who
design their formulas around the question "OK, how can I stick in a
double-negation in the answer?"

=SUMPRODUCT((MOD(ROW(A3:A12)-ROW(A3),8)=0)*(A3:A12))
Of course, personally, I am indifferent between the above and the array
formula
=SUM(IF(MOD(ROW(A3:A12)-ROW(A3),8)=0,A3:A12))

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , no-
says...
In news:microsoft.public.excel.worksheet.functions, "RagDyeR"
posted on Sat, 22 Jan 2005 13:21:14 -0800:

You should now be able to understand why all of the 3 formulas I entered
above will work for your situation.

Jeez. Seems like it would make more sense to have a "step" command in
Excel.

=SUM(T12:T428(STEP(8))

Or something like that. :-)

Thanks for the information. I will study it carefully.

Damaeus