I am trying to develop a sales projection worksheet. There are a
number of programs we sell each providing a different number of months
of service. What I want to do is have the user input the number of
sales for each month for each program in a row. I then want to total
the number of subscribers for each month by adding that value to each
month for the duration of their subscription. The number of months
duration is defined in a separate cell. I have been playing around
with this for several days and getting nowhere fast. Any help would be
greatly appreciated.

Sales Jan Feb Mar Apr

6 Month Subscription 1 0 0 0
12 Month Subscription 1 0 0
------------------------------------------------------------------------------------------------------
# Subscribers 1 2 2 2
.... ....

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

Here is a more extensible approach that can easily be expanded to
accommodate additional products.

Assume your sales forecasts are in a worksheet called Sales with the
month in row 1, the product ID in column A, the subscription duration
in column B.

Set aside row 2 for a helper row, which you can hide later.

Set aside as many columns from C over as you will need for your
forecast of open subscriptions. Let's say that you want a 12-month
forecast. That will occupy as far as column N.

Leave column O blank, as a separator column.

Enter your sales forecasts starting in column P, ensuring that your
first month of sales is at least N months prior to the first month for
which you want an open subscriptions calculation, where N is your
longest subscription.

C1: Enter the first month to be calculated, in exact same form as
months are represented in Sales area (column P and over). The
following months can be derived by formula from this if you wish, or
set up as constants. If you derive them by formula, you can just change
the first month (C1) to move your forecast to start at a different
month. See

http://www.cpearson.com/excel/datearith.htm

for how add a month to a date. You could also have set up C1 as a
formula to automatically generate the current month, or next month.

C2: =MATCH(C1,$O$1:$IV$1,0)-1 and copy across for all months, 12
months or however long you are calculating subscriptions

A3 and down: first product IDs

B3 and down: Subscription duration for product in column A

D3: =SUM(OFFSET($O3,0,C$2+1-$B3,1,$B3)) copy across for all forecast
months (to column N if 12 months) and down for all products.

You should now have the open subscriptions for each month. You can sum
them as you wish, either in total or by duration (with SUMIF). You can
color the subscription area to differentiate it from the Sales entry
area. You should also protect the sheet, unlocking only the sales
entry cells.

One question I had is whether a sale is counted as a subscription in
the month of sale or the month after. If it is the month after, then
you should change the -1 in row 2 to -2.

I had started with two sheets, one for Sales and the other for
Subscriptions but that has the drawback that you could easily insert a
new product in Sales and neglect to insert it in Subscriptions. This
way, a new product insertion affects both areas simultaneously since
there is just one row per product.

You can also delete columns to the right of the separator column (O)
without affecting the formulas.

Hope this helps

Declan O'R

Thanks for the help Declan. I had not even thoguht of using Match. I
will get to work now and check it out.
DOR wrote:
Here is a more extensible approach that can easily be expanded to
accommodate additional products.

Assume your sales forecasts are in a worksheet called Sales with the
month in row 1, the product ID in column A, the subscription duration
in column B.

Set aside row 2 for a helper row, which you can hide later.

Set aside as many columns from C over as you will need for your
forecast of open subscriptions. Let's say that you want a 12-month
forecast. That will occupy as far as column N.

Leave column O blank, as a separator column.

Enter your sales forecasts starting in column P, ensuring that your
first month of sales is at least N months prior to the first month for
which you want an open subscriptions calculation, where N is your
longest subscription.

C1: Enter the first month to be calculated, in exact same form as
months are represented in Sales area (column P and over). The
following months can be derived by formula from this if you wish, or
set up as constants. If you derive them by formula, you can just change
the first month (C1) to move your forecast to start at a different
month. See

http://www.cpearson.com/excel/datearith.htm

for how add a month to a date. You could also have set up C1 as a
formula to automatically generate the current month, or next month.

C2: =MATCH(C1,$O$1:$IV$1,0)-1 and copy across for all months, 12
months or however long you are calculating subscriptions

A3 and down: first product IDs

B3 and down: Subscription duration for product in column A

D3: =SUM(OFFSET($O3,0,C$2+1-$B3,1,$B3)) copy across for all forecast
months (to column N if 12 months) and down for all products.

You should now have the open subscriptions for each month. You can sum
them as you wish, either in total or by duration (with SUMIF). You can
color the subscription area to differentiate it from the Sales entry
area. You should also protect the sheet, unlocking only the sales
entry cells.

One question I had is whether a sale is counted as a subscription in
the month of sale or the month after. If it is the month after, then
you should change the -1 in row 2 to -2.

I had started with two sheets, one for Sales and the other for
Subscriptions but that has the drawback that you could easily insert a
new product in Sales and neglect to insert it in Subscriptions. This
way, a new product insertion affects both areas simultaneously since
there is just one row per product.

You can also delete columns to the right of the separator column (O)
without affecting the formulas.

Hope this helps

Declan O'R

When I laid out Declan's proposed solution I got a strange result in
the middle rows. It worked fine for rows 3-5 and 9-11 but I got crazy
numbers in rows 6-8. Everything seems OK when you compare formulas to
rows that seem to work. Any Ideas? Also I was hoping to put both
tables on top of each other rather than side by side in the same row.
Any ideas? Thanks.

Send me your spreadsheet and I will take a look at it. As for putting
the spreadsheets above and below each other (I assume that's what you
mean by "on top of each other", that should be possible, with the same
proviso that you have to be carefull to add a new product to both
tables.

Declan