Auto Front-Loaded Distribution in Excel

[rygar24]

I am trying to distribute the Total Number of hours required on a project throughout the life of the project. For example: Project X1 has 2160 total hours required throughout the life of the project. With a start of Mar 14 and close of Aug 15, it runs for 18 months. I am trying to figure out if there is a formula I can use to spread that 2160 hours over the life of the project. However, this distribution will not be done evenly. I am trying to have it front-loaded and then gradually decreasing until its close in Aug 2015. So with having the total hours needed, is there a formula that can spread the total over the 18 months with it gradually decreasing to its close? Please help! Thank you!</SPAN></SPAN>

 

[jamcall]

You could either do a linear reduction in hours or use some type of distribution for a more gradual change in hours. Below, I provided an example with both linear and a normal distribution...

# Hours	# Months	Linear Hours	Norm Dist	Norm Hours
2160	18	227.3684211	0.997355701	230.067402
		214.7368421	0.987782386	227.859055
		202.1052632	0.959610268	221.360384
		189.4736842	0.914430916	210.93853
		176.8421053	0.854730735	197.167048
		164.2105263	0.783664452	180.773664
		151.5789474	0.704779689	162.576734
		138.9473684	0.621725982	143.418122
		126.3157895	0.537981153	124.100084
		113.6842105	0.456622713	105.332532
		101.0526316	0.380163455	87.6951106
		88.42105263	0.310459981	71.6160958
		75.78947368	0.248692847	57.3678149
		63.15789474	0.195408462	45.0763125
		50.52631579	0.150607239	34.7416835
		37.89473684	0.113859882	26.2648996
		25.26315789	0.084434128	19.4770435
		12.63157895	0.061416875	14.167484
	Total	2160		2160

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=2 width=84><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY>
</TBODY>

The table starts in cell A1. In col C is the formula, =$A$2*($B$2+2-ROW())/$B$2/SUMPRODUCT(ROW(INDIRECT("1:"&$B$2))/$B$2)
This gives a linear decrease in hours.

In col D is a normal distribution with the formula, =NORMDIST(($B$2+2-ROW())/$B$2,1,0.4,FALSE)
0.4 is the standard deviation I used... this affects the slope of your curve, lower value gives steeper drop-off.
This is just the right hand side of a normal distribution curve.

In col E the normal distribution is applied to the hours with the formula, =$A$2*$D2/SUM($D$2:$D$19)

The bottom row is just a sum of the rows above to verify that the distribution does, indeed, add up to the alloted number of hours.

HTH,
~ Jim

 

[kvmahesh72]

hi,
The table starts in cell A1. In col C is the formula, =$A$2*($B$2+2-ROW())/$B$2/SUMPRODUCT(ROW(INDIRECT("1:"&$B$2))/$B$2)
This gives a linear decrease in hours.

can you share similar for linear increase formula too