Formula for distributed values based on % of Effort

aviator09

New Member
Joined
Jul 19, 2017
Messages
12
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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"]
<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.

Asumming your first table is in Sheet1 and second table in Sheet2:

D2 = IFERROR($C2*INDEX(Sheet2!$A$3:$E$3,5-(MATCH(TEXT($B2,"MMM"),$D$1:$O$1,0)-MATCH(D$1,$D$1:$O$1,0))),"")
 
Upvote 0
This will be in one sheet only. The values I have posted in the month columns are just hard coded values of what they should be if a formula had applied them. Is there a way to upload my excel file if that would help? My thought is I enter the hours in the "Hours Est" cell and hit enter. At that point, formulas in each cell of the months columns would take those hours, look at the releae date, and then apply the logic accordingly.
 
Upvote 0
This will be in one sheet only. The values I have posted in the month columns are just hard coded values of what they should be if a formula had applied them. Is there a way to upload my excel file if that would help? My thought is I enter the hours in the "Hours Est" cell and hit enter. At that point, formulas in each cell of the months columns would take those hours, look at the releae date, and then apply the logic accordingly.

then just change the references for the second table..
 
Upvote 0
Worked perfectly! Thank you very much. I was also able to work the formula to increase the # of months and work the dates forward as well.

One question of curiosity: The second table that has the %'s. The formula reads it from right to left. How would you alter the formula to read the %'s from left to right? I could not figure that part out when playing with the formula.

Otherwise, than you again for your very quick solution. It's already been put to use.
 
Upvote 0
Worked perfectly! Thank you very much. I was also able to work the formula to increase the # of months and work the dates forward as well.

One question of curiosity: The second table that has the %'s. The formula reads it from right to left. How would you alter the formula to read the %'s from left to right? I could not figure that part out when playing with the formula.

Otherwise, than you again for your very quick solution. It's already been put to use.


just remove the "5-" in the formula..
 
Upvote 0
Perfect. Formula is working great, and I was able to figure out how to add an additional IF statement to determine if the project is active. Thanks again for all your help. It is greatly appreciated.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top