Distribute a number across cells

[Sandeep Warrier]

Hi Everyone,

Wish you all a happy new year.

I'm looking for a formula solution for this question. Below is some sample data.

If Type = Flat, Value is distributed evenly across months, starting from Report Month to End Date. --> Done
If Type = Bell, distribution of Value follows a Bell Curve.
If Type = Early Peak, distribution starts from a lower value, goes high and then starts getting low again.
If Type = Late Peak, this is the opposite of Early Peak.
If Type = Front Loaded, distribution starts at a high and then reduces.
If Type = Back Loaded, this is the opposite of Front Loaded.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Report Month	15-Dec-14
2	Start Date	15-Nov-14
3	End Date	15-Mar-15
4
5
6		Type	Value	Nov-14			Dec-14			Jan-15			Feb-15			Mar-15			Apr-15			May-15			Jun-15
7		Flat	50	0			12.5			12.5			12.5			12.5			0			0			0
8		Bell	50	0			10			15			15			10			0			0			0
9		Early Peak	50	0			10			20			15			5			0			0			0
10		Late Peak	50	0			5			15			20			10			0			0			0
11		Front Loaded	50	0			20			15			10			5			0			0			0
12		Back Loaded	50	0			5			10			15			20			0			0			0
Sheet1

Excel 2010

 

[cyrilbrd]

Hi, just curious, how do you distribute the values?
For instance for Bell, here the distribution is 20%, 30%, 30% 20%.
What would it be for the period 15-Dec-14 to 15-Feb-14:
30%, 40%, 30%?
25%, 50%, 25%?
Thanks...

 

[Sandeep Warrier]

Hi,

The distribution % in the sample was just a guess. The actual distribution % does not matter as long as it follows the curve. I didn't go the way of pre-setting the distribution % because the month diff can go up to 24-36 months.

I did end up with a way to do this. I'm interested in seeing if anyone can optimize this further.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Report Month	15-Dec-14
2	Start Date	15-Nov-14
3	End Date	15-Mar-15
4
5
6		Type	Value	Nov-14			Dec-14			Jan-15			Feb-15			Mar-15
7				Forecast	Planned	Actual	Forecast	Planned	Actual	Forecast	Planned	Actual	Forecast	Planned	Actual	Forecast	Planned	Actual
8		Flat	100				25			25			25			25
9		Bell	100				19.098301			30.901699			30.901699			19.098301
10		Early Peak	100				26.666667			53.333333			13.333333			6.6666667
11		Late Peak	100				6.6666667			13.333333			53.333333			26.666667
12		Front Loaded	100				40			30			20			10
13		Back Loaded	100				10			20			30			40
My Formula

Excel 2010

Cell Formulas
Range	Formula
D8	=IF(ISNUMBER(D$6)*(DATE(YEAR($B$1),MONTH($B$1),1)<=D$6),IF($B8="flat",IFERROR(INDEX((ROW($A$1:INDEX($A:$A,Mcount))^0)*($C8/Mcount),ColNum),0),IF($B8="Bell",IFERROR(INDEX((SIN(RADIANS((ROW($A$1:INDEX($A:$A,Mcount))*(180/(Mcount+1))))) /SUMPRODUCT(SIN(RADIANS((ROW($A$1:INDEX($A:$A,Mcount)))*(180/(Mcount+1)))))) * $C8,ColNum),0),IF($B8="Early Peak",IFERROR(INDEX((2^LARGE(ROW($A$1:INDEX($A:$A,Mcount)),ROW($A$1:INDEX($A:$A,Mcount)))/(-2*(1-2^Mcount)))*$C8,IF(ColNum=2,1,IF(ColNum=1,2,ColNum))),0),IF($B8="Late Peak",IFERROR(INDEX((2^ROW($A$1:INDEX($A:$A,Mcount))/(-2*(1-2^Mcount)))*$C8,IF(ColNum=Mcount-1,Mcount,IF(ColNum=Mcount,Mcount-1,ColNum))),0),IF($B8="Front Loaded",IFERROR(INDEX(LARGE(ROW($A$1:INDEX($A:$A,Mcount)),ROW($A$1:INDEX($A:$A,Mcount)))*($C8/(Mcount*(Mcount+1)/2)),ColNum),0),IF($B8="Back Loaded",IFERROR(INDEX(ROW($A$1:INDEX($A:$A,Mcount))*($C8/(Mcount*(Mcount+1)/2)),ColNum),0),0)))))),"")

Excel Workbook
Name	Refers To
ColNum	=COUNTIFS('My Formula'!$D$7:A$7,"Forecast",'My Formula'!$D$6:A$6,">="&DATE(YEAR('My Formula'!$B$1),MONTH('My Formula'!$B$1),1))
Mcount	=ROUND(('My Formula'!$B$3-'My Formula'!$B$1)/30,)+1
Workbook Defined Names

 

[Sandeep Warrier]

I know the named range MCount can use DATEDIF too, but was just experimenting

 

[shg]

NASA's Beta polynomial was designed for exactly this purpose. There's a workbook that describes it, and two UDFs that implement it, at https://app.box.com/shared/ipkvdyc4bt

 

[Sandeep Warrier]

Thanks shg... looks exactly like what I need!

 

[shg]

You're welcome, glad it works for you.

 

[StephenCrump]

shg

Thanks for posting this.

I haven't seen NASA's beta curve before, and your workbook demonstrates it nicely.

 

[shg]

Thank you, Stephen.