Calculating Experience / Learning Curve Series

[PDNY]

Hello to all,

I hope someone has seen this problem before and my explanation is clear.

BACKGROUND: I'm trying to have Excel calculate periodic (in this case monthly) sums for sales people hired at different periods where each one will be productive according to a calculated experience curve.

To give an example, let's say the experience curve is as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[/TR]
[TR]
[TD]Expected Product A sales[/TD]
[TD]$100[/TD]
[TD]$200[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]Expected Product B sales[/TD]
[TD]$10[/TD]
[TD]$20[/TD]
[TD]$30[/TD]
[/TR]
</tbody>[/TABLE]

And salespersons are placed in the field in this order:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]New salespersons added[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The sales results would be:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Expected Product A sales[/TD]
[TD]$200[/TD]
[TD]$500[/TD]
[TD]$800[/TD]
[/TR]
[TR]
[TD]Expected Product B sales[/TD]
[TD]$20[/TD]
[TD]$50[/TD]
[TD]$80[/TD]
[/TR]
[TR]
[TD]Total sales[/TD]
[TD]$220[/TD]
[TD]$550[/TD]
[TD]$1100[/TD]
[/TR]
</tbody>[/TABLE]

This means that once a sales person is in the field, that person is expected to generation Product A and Product B sales of $100 and $10, respectively, her first month and $200 and $20, respectively, her second month.

Note that not all sales persons are placed in the field at once hence they will have different sales results if they started selling at different periods.

In summary, I've viewed each period as sumproducts of two arrays:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Monthly product sales[/TD]
[TD]{100 + 10, 200 + 20, 300 + 30}[/TD]
[/TR]
[TR]
[TD]New salespersons additions[/TD]
[TD]{2, 1, 0}[/TD]
[/TR]
</tbody>[/TABLE]

Whereby each group of salespersons is indexed to a period's expected product sale and summed to give the period's total sale.

PROBLEM: While the above simple scenario is easily calculable with direct cell references, the problem is that Excel restricts cells to 255 characters, which we surpass once additional products and periods are added. I've used sales persons but the same problem could apply to an open enrollment course series, baseball RBIs or anything else

I've looked into using INDEX-MATCH as well as CHOOSE functions but they seem to work best where the result is for a single point, say, for one salesperson or for all salespersons starting in the same period. I can't say I've fully figured out applying them either.

I hope this is clear and someone can help out. Any suggestions are much appreciated.

Thanks in advance.

 

[PDNY]

RESOLVED: For anyone interested, the relevant function is SUMPRODUCT using N, OFFSET and COLUMN functions in the below example formula to reverse the SUMPRODUCT array.

=SUMPRODUCT($A$1:A1,SUBTOTAL(9,OFFSET($A$2,,COLUMNS($A$2:A2)-(COLUMN($A$2:A2)-COLUMN($A$2)+1),,1)))

The full Q&A on this forum can be found here:

http://www.mrexcel.com/forum/showth...product-question&highlight=reverse+sumproduct

or here:

http://www.mrexcel.com/forum/showth...DUCT-alternative&highlight=sumproduct+reverse

 

[PDNY]

Or here:

http://www.mrexcel.com/forum/showthread.php?86817-Sumproduct-question&highlight=sumproduct+reverse