Multiple Months (in columns) Repayments calculated in a single row

[Ecell]

Hi,

I have a schedule with many rows (rows are products) against which users input sales by month in columns. These sale amounts are then spread over a period (eg 12 months). See below 1,200 would be spread 100 per month for 12 months from Aug. The problem I am having is in trying to add to the 100 from another 50 per month for 12 months from Nov. I can do this in multiple rows and reconsolidate but I am trying to achieve a result on the same row against which the sales are entered.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]12 month Repay[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]0[/TD]
[TD]1200[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]600[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]RePMT[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. In C2:

=SUM($B$2:B2)/12

Copy across.

 

[steve the fish]

Oh that wont work at the far end.... hold on that may be more difficult

 

[steve the fish]

This in C2 should work:

=SUM(INDEX(2:2,MAX(2,COLUMN(B1)-11)):INDEX(2:2,COLUMN(B1)))/12

 

[Ecell]

Thanks for the response, much appreciated - it works great if on its own in a blank sheet but I break it when trying to modify the array to be a fixed area (the original sales data sits in the first 60+ columns to the left) because I don't quite understand it and how the column references should operate. I am a novice with index'ing but I do use simple Index and matching to return the intersect of a row and col value.

First point of confusion, why are the column references in the "Row" part of the index (Index(Array,ROW,[Col] etc I would have thought you would be shifting the columns. Secondly, what's the purpose of max'ing it at 2 ?

 

[steve the fish]

We are trying to create a moving range of 12 cells so any information before a year is ignored. However for the first 11 months it's obviously not possible to use 12 months. We simply dont have 12 months of data yet. So thats the reason for the max. It stops the formula erroring essentially. This formula is position dependant ie it needs to be altered depending on where it is placed in the spreadsheet.