Get Your Thinking Caps On!

[lbird2]

[TABLE="class: grid, width: 1059"]
<colgroup><col><col span="13"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Sales Pattern[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"]1%[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD="align: right"]287[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]532[/TD]
[TD="align: right"]612[/TD]
[TD="align: right"]751[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]535[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD="align: right"]287*3%[/TD]
[TD="align: right"]287*10%
+ 500*3%[/TD]
[TD="align: right"]287*21%
+ 500*10%
+ 532*3%[/TD]
[TD="align: right"]287*25%
+ 500*21%
+ 532*10%
+ 612*3%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The hardest part of this may be the explaining.

I'm trying to find the most efficient way of converting production to sales based on a sales pattern.

Thirteen months across the top. The sales pattern shows that 3% of things produced in a given month will be sold withing the same month. 10% will be sold in the next month, and so on.

The Production row shows what was produced for each month. So to convert this to sales, month 0 will be just month 0 production time 3%. Month 1 will be (month 0 production times 10%) + (month 1 production times 3%), etc.

As you can see with the formula I have going, this is going to get nasty!

There has to be a better solution and I can think of no better place to find it. Thanks in advance.

 

[Nine Zero]

Would this formula work?

Assuming your data begins in A1

formula in B4
=B2*B3

formula in C4 dragged to the end to the right
=(C$2*C$3)+B$4

 

[MrKowz]

In the workbook you want this to work in, enter the VBA editor (alt+f11) and create a new module. Copy/Paste this code into that module:

Public Function SalesPattern(sPattern As Range, sProduction As Range) As Double
Dim i       As Long
For i = 1 To sPattern.Count
    SalesPattern = SalesPattern + sProduction(sProduction.Count + 1 - i) * sPattern(i)
Next i
End Function

Now, use the following formula (cell B4, copy it to the right). I included a handful of manual entry formulas as a double-check.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	0	1	2	3	4	5	6	7	8	9	10	11	12
2	Sales Pattern	3%	10%	21%	25%	8%	9%	10%	5%	2%	3%	2%	1%	1%
3	Production	287	500	532	612	751	235	535	128	91	18	0	0	0
4	Sales	8.61	43.7	126.23	248.31	343.41	409.5	466.52	455.63	385.73	363.07	253.94	187.04	140.44
5	Manual Entry	8.61	43.7	126.23	248.31
Sheet1

Cell Formulas
Range	Formula
B4	=salespattern($B$2:B2,$B$3:B3)
B5	=B3*B2
C4	=salespattern($B$2:C2,$B$3:C3)
C5	=C3*B2+B3*C2
D4	=salespattern($B$2:D2,$B$3:D3)
D5	=D3*B2+C3*C2+B3*D2

 

[Scott T]

try Copy across

=SUMPRODUCT($B$2:B2,$B$3:B3)

 

[lbird2]

The function worked perfectly. Thank you!!

So did the others. You guys are great.