Sum Daily Project Hours based on Project Priority to 8 per day

Hi,

I want to spread the Estimated hours in C column across the 5 days in each row, based on what priority was chosen in B column. So,

[Please note the weights are approximate and the below text is for the test sheet. However, you can refer to the main sheet for the real problem.]

• 'High' priority will get max weightage of 1, i.e. more hours to complete project.
• 'Medium' priority will get medium weightage of 0.67 i.e. medium hours to complete project.
• 'Low' priority will get the lowest weightage of 0.34 i.e least hours to complete project.

Now, the total number of hours (Row 8) for each day should not exceed 8 hours per day. so each column cell value has to be adjusted automatically.
can someone help me with the formula to be inputted in E3:I7 ?

The file which contains all the formulae is attached for your perusal.

i provided just a snapshot of 40 hours per week (5 days), but the Est. Hours for each project may be spread across more weeks (see main sheet), depending upon how long each project runs. i am trying to make sense of how to spread the hours vertically i.e. not more than 8 hours per day. So need some formula that would automatically adjust the column cells hours (daily hours), so that their total summation only adds upto not more than 8 hours.

Thanks.

It kind of makes sense, but it would help us understand your logic better if you typed the expected results into your table.