Newbie needs help with incrementing a series of cells
The following is a rather crude solution but it might be the basis for
a better one - I am looking at trying to get something better.
Assume you have the program length in column B for each product.
Simpler solution - assume you have sales for each month up to the
maximum program lenth prior to your start month (say this mont). Fill
those values in the appropriate columns, starting at column C, row 3,
as necessary. I am assuming that your longest program is 12 months.
If column C represents 11 months ago, thismonth will be column N.
In your total row in column N put
=SUM(OFFSET(N3,0,-$B$3+1,1,$B$3),OFFSET(N4,0,-$B$4+1,1,$B$4))
where your first product is in row 3 and second in row 4.
Unfortunately this will require a unique OFFSET term for each product,
which could be unacceptable if you have a lot of products.
You could also dispense witht he prior months' data if you were
starting from scratch but the column's are useful, otherwise you would
need a ridiculously complex formula like the following, to avoid
extending back prior to your first month, and maybe off the
spreadsheet!:
=SUM(OFFSET(C3,0,-MIN($B$3-1,COLUMN()-COLUMN($C$3)),1,MIN($B$3,COLUMN()-COLUMN($C$3)+1)),OFFSET(C4,0,-MIN($B$4-1,COLUMN()-COLUMN($C$4)),1,MIN($B$4,COLUMN()-COLUMN($C$4)+1)))
Ugh!
There may be a better array-based approach, but I don't see it yet. In
the meantime, a better non-array based approach would be to establish
another parallel Month/Product matrix further over in your spreadsheet,
or on another sheet, to sum the number of current programs for each
product each month. That would be easy, using the SUM of the OFFSET
range used in the first formula above for each product, each month. If
you want any more information on that, please let me know.
In the meantime, I'll look at some array solutions, or maybe someone
else will come up with one.
Declan O'R
|