Get Your Thinking Caps On!

[lbird2]

Month	0	1	2	3	4	5	6	7	8	9	10	11	12
Sales Pattern	3%	10%	21%	25%	8%	9%	10%	5%	2%	3%	2%	1%	1%
Production	287	500	532	612	751	235	535	128	91	18	0	0	0
Sales	287*3%	287*10% + 500*3%	287*21% + 500*10% + 532*3%	287*25% + 500*21% + 532*10% + 612*3%

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>

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.