Formula for distributed values based on % of Effort

A

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
aviator09 said:
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.
Click to expand...

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
aviator09 said:
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.
Click to expand...

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
aviator09 said:
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.
Click to expand...


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
You must log in or register to reply here.

Similar threads

R
  • Question Question
Distribute Hours Evenly Between 2 Dates but Only On Working Days
Replies
2
Views
822
RockFish74
R
leopardhawk
  • Question Question
Can't understand why two values are different
Replies
6
Views
1K
sandy666
sandy666
K
  • Question Question
Need help with creating a lookup based on certain criteria
Replies
2
Views
570
RasGhul
RasGhul
Ironman
  • Question Question
Two complex formulas returning incorrect values
2
Replies
11
Views
3K
Ironman
Ironman
B
  • Question Question
Master Schedule: Conditional Format based on Date
Replies
3
Views
934
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

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