Looking for help on how to take an estimate of hours in onecell, and spread it across five cells (months) with varied weight in terms of %of the hours estimated. In this example,engineers get assigned to a project and have five months to work on it prior to therelease date. They start out with a 40%investment of time five months from the release date, then to 30% four monthsout, 20% three months out, and 5% out the last two months.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1011"]
<colgroup><col width="92" style="width:69pt"> <col width="88" style="width:66pt"> <col width="64" style="width:48pt"> <col width="31" style="width:23pt"> <col width="29" style="width:22pt"> <col width="36" style="width:27pt"> <col width="32" style="width:24pt"> <col width="35" style="width:26pt"> <col width="31" style="width:23pt"> <col width="27" style="width:20pt"> <col width="34" style="width:26pt"> <col width="29" style="width:22pt"> <col width="32" style="width:24pt"> <col width="36" style="width:27pt"> <col width="31" style="width:23pt"> <col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 92, bgcolor: transparent"]Project Name[/TD]
[TD="width: 88, bgcolor: transparent"]Release Date[/TD]
[TD="width: 64, bgcolor: transparent"]Hours Est[/TD]
[TD="width: 31, bgcolor: #E2EFDA"]JAN[/TD]
[TD="width: 29, bgcolor: #E2EFDA"]FEB[/TD]
[TD="width: 36, bgcolor: #E2EFDA"]MAR[/TD]
[TD="width: 32, bgcolor: #E2EFDA"]APR[/TD]
[TD="width: 35, bgcolor: #E2EFDA"]MAY[/TD]
[TD="width: 31, bgcolor: #E2EFDA"]JUN[/TD]
[TD="width: 27, bgcolor: #E2EFDA"]JUL[/TD]
[TD="width: 34, bgcolor: #E2EFDA"]AUG[/TD]
[TD="width: 29, bgcolor: #E2EFDA"]SEP[/TD]
[TD="width: 32, bgcolor: #E2EFDA"]OCT[/TD]
[TD="width: 36, bgcolor: #E2EFDA"]NOV[/TD]
[TD="width: 31, bgcolor: #E2EFDA"]DEC[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 320, bgcolor: #D0CECE, colspan: 5"]Monthly Weight Spread[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Project A[/TD]
[TD="bgcolor: transparent"]9/15/2017[/TD]
[TD="bgcolor: transparent"]300[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]120[/TD]
[TD="bgcolor: transparent"]90[/TD]
[TD="bgcolor: transparent"]60[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]5 months[/TD]
[TD="bgcolor: transparent"]4 months[/TD]
[TD="bgcolor: transparent"]3 months[/TD]
[TD="bgcolor: transparent"]2 months[/TD]
[TD="bgcolor: transparent"]1 month[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Project B[/TD]
[TD="bgcolor: transparent"]11/30/2017[/TD]
[TD="bgcolor: transparent"]500[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]200[/TD]
[TD="bgcolor: transparent"]150[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]40%[/TD]
[TD="bgcolor: transparent"]30%[/TD]
[TD="bgcolor: transparent"]20%[/TD]
[TD="bgcolor: transparent"]10%[/TD]
[TD="bgcolor: transparent"]10%[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The weighted hours are distributed accordingly in thecorresponding month columns based on the % distribution. The release month is the trigger to keyfrom. I cannot use VBA code or macros inthe work environment, so hoping this can be accomplished via a formula.
Thank you all for your time and help.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1011"]
<colgroup><col width="92" style="width:69pt"> <col width="88" style="width:66pt"> <col width="64" style="width:48pt"> <col width="31" style="width:23pt"> <col width="29" style="width:22pt"> <col width="36" style="width:27pt"> <col width="32" style="width:24pt"> <col width="35" style="width:26pt"> <col width="31" style="width:23pt"> <col width="27" style="width:20pt"> <col width="34" style="width:26pt"> <col width="29" style="width:22pt"> <col width="32" style="width:24pt"> <col width="36" style="width:27pt"> <col width="31" style="width:23pt"> <col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 92, bgcolor: transparent"]Project Name[/TD]
[TD="width: 88, bgcolor: transparent"]Release Date[/TD]
[TD="width: 64, bgcolor: transparent"]Hours Est[/TD]
[TD="width: 31, bgcolor: #E2EFDA"]JAN[/TD]
[TD="width: 29, bgcolor: #E2EFDA"]FEB[/TD]
[TD="width: 36, bgcolor: #E2EFDA"]MAR[/TD]
[TD="width: 32, bgcolor: #E2EFDA"]APR[/TD]
[TD="width: 35, bgcolor: #E2EFDA"]MAY[/TD]
[TD="width: 31, bgcolor: #E2EFDA"]JUN[/TD]
[TD="width: 27, bgcolor: #E2EFDA"]JUL[/TD]
[TD="width: 34, bgcolor: #E2EFDA"]AUG[/TD]
[TD="width: 29, bgcolor: #E2EFDA"]SEP[/TD]
[TD="width: 32, bgcolor: #E2EFDA"]OCT[/TD]
[TD="width: 36, bgcolor: #E2EFDA"]NOV[/TD]
[TD="width: 31, bgcolor: #E2EFDA"]DEC[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 320, bgcolor: #D0CECE, colspan: 5"]Monthly Weight Spread[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Project A[/TD]
[TD="bgcolor: transparent"]9/15/2017[/TD]
[TD="bgcolor: transparent"]300[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]120[/TD]
[TD="bgcolor: transparent"]90[/TD]
[TD="bgcolor: transparent"]60[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]5 months[/TD]
[TD="bgcolor: transparent"]4 months[/TD]
[TD="bgcolor: transparent"]3 months[/TD]
[TD="bgcolor: transparent"]2 months[/TD]
[TD="bgcolor: transparent"]1 month[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Project B[/TD]
[TD="bgcolor: transparent"]11/30/2017[/TD]
[TD="bgcolor: transparent"]500[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]200[/TD]
[TD="bgcolor: transparent"]150[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]40%[/TD]
[TD="bgcolor: transparent"]30%[/TD]
[TD="bgcolor: transparent"]20%[/TD]
[TD="bgcolor: transparent"]10%[/TD]
[TD="bgcolor: transparent"]10%[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The weighted hours are distributed accordingly in thecorresponding month columns based on the % distribution. The release month is the trigger to keyfrom. I cannot use VBA code or macros inthe work environment, so hoping this can be accomplished via a formula.
Thank you all for your time and help.