SUM the Previous n Cell Values

[Simonc64]

Hi Guys

Been struggling with this on and off for a few days and finally given in! Seems so simple but cant get my head round the logic!

Column A contains numerical values (for examples sake lets say the column runs from A2:A36) A1 is a text heading

In Column B I need a formula that i can run thru cells B2:B36 that SUMS every 6th value (B1 is a text heading)

I have tried the OFFSET formula as follows =SUM(OFFSET(A7,(ROW()-8)*6,0,6,1)),"") which works fine for rows B2:B6, but of course when the formula reached B12 it falls over because the offset references are absolute and dont change.

Tearing my hair out so ANY help welcomed!

Thanks

Simon

 

[JACOBB9900]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]Row5
[/TD]
[TD]Hrs per piece
[/TD]
[TD]1
[/TD]
[TD].5
[/TD]
[TD].8
[/TD]
[TD].5
[/TD]
[TD]8
[/TD]
[TD]120
[/TD]
[TD]2
[/TD]
[TD]2.5
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Row6
[/TD]
[TD]Total Hrs
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1.6
[/TD]
[TD]1
[/TD]
[TD]16
[/TD]
[TD]240
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Row7
[/TD]
[TD]Total Days
[/TD]
[TD]33
[/TD]
[TD]33
[/TD]
[TD]33
[/TD]
[TD]33
[/TD]
[TD]32
[/TD]
[TD]30
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]
Total Pieces=2


Here’s what we’re looking at. We are going off of 8 hourworks days. Every time the sum is close to equaling 8 without going over in Row6 a day is added in Row 7 starting from the right. There are 30 days in cell J7because it will take 240 hours (30 days) to process two pieces. In summary I needto add a day to Row 7 for every ~8 hours that are summed up in Row 6.

Thank you.

 

[Peter_SSs]

I am using Internet Explorer.

In that case you should have another option for posting a grid like you did. Follow the Look here link in my signature block below and then the Borders-Copy-Paste link.

In relation to you latest sample I'm afraid I still don't fully understand. Can you step though a few more examples, starting from the first cell (M7?)
Why is that cell 1 when the total so far in row 6 is only 2 - not "close to equalling 8"?
Then explain the calculation for L7 being 1, K7 being 2 and J7 being 30 (I thought J7 should be more than 30 going by your previous descriptions)

 

[JACOBB9900]

Thank you for your help. I was able to find a solution to my problem.

 

[Peter_SSs]

Thank you for your help. I was able to find a solution to my problem.

Thanks for letting us know.