Challenge! balance of numbers over months!

[dannyok90]

Hi All,

I have a challenge for you.



Im trying to come up with a way to balance hours over a period of time:


30 hours over 6 months for example
now, easily thats 5 hours a month.

but if i used a scale of 1-10:

10 meaning the majority of hours were going to be used at the end of the six months
1 meaning the majority of hours were going to be used at the start of the six months


if i set the balance to 1 (majority of hours to be used at the start)

how could i get excel to divvy the rest of the hours up going down in scale until the final (6th) month?

I've been at this for hours and cant think of anything, i've come close but no cigar!

any help?

Thanks, Dan

 

[MickG]

Perhaps:-An Arithmetical Progression (adding common difference)

 

[jim may]

FWIW....

Excel 2010

	A	B	C	D	E	F	G
One Method of Allocation is known as "SUM OF THE DIGITS" which takes the Sum of the Periods and
for whatever period you want to use, say 6 (Months, Years,, whatever) allows you to apportion as:
6/21, 5/21, 4/21, 3/21, 2/21 and finally 1/21
Total Hours to Allocate
Over Number of Periods
Sum of Digits - where x = 6
Periods (below)

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/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="align: right"][/TD]

[TD="align: center"]2[/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="align: center"]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="align: center"]4[/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="align: center"]5[/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="align: right"][/TD]

[TD="align: center"]6[/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="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Increasing[/TD]
[TD="align: center"]Decreasing[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"](x²+x)/2[/TD]
[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]1.428571429[/TD]
[TD="align: right"]8.571428571[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]2.857142857[/TD]
[TD="align: right"]7.142857143[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]4.285714286[/TD]
[TD="align: right"]5.714285714[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]5.714285714[/TD]
[TD="align: right"]4.285714286[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]7.142857143[/TD]
[TD="align: right"]2.857142857[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]8.571428571[/TD]
[TD="align: right"]1.428571429[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sum >>>[/TD]
[TD="align: right"]30.00000[/TD]
[TD="align: right"]30.00000[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=((C8*C8)+C8)/2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F11[/TH]
[TD="align: left"]=($C$7*($E11/$C$9))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G11[/TH]
[TD="align: left"]=($C$7*(OFFSET($E$11,16-ROW(),0)/$C$9))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F18[/TH]
[TD="align: left"]=SUM(F11:F16)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G18[/TH]
[TD="align: left"]=SUM(G11:G16)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]