Spread the number of hrs available proportionately to the available hrs at each interval

I'm not sure if this is what you want, but I've enlisted the help of some helper cells.

Cell E34 is the hours of work available (assuming that people work one hour per hour). The formula is: =SUMIFS($E$2:$E$33,$E$2:$E$33,">5") which is the sum of the available hours ind column E, but only for those cells where the number is greater than 5.

Column F is a helper column with the formula: F(E2>5,E2/$E$34,0). This prorates the work by number of people available - the work is allocated only if the number of people for that hour is greater than 5. The total of this column is 100%.

Column G is the number of hours sorted out by Percent * Total Hours - this column adds up toe the 11.81 hours.

As for the warning, I have this formula in cell E35: =IF(E34>B34,"Workload OK","Too much work") - Basically it says that if the work you have available exceeds the work you have to do, you're OK.

how much work can 25 people really get done in 15 minutes? I just spent more than 10 trying to figure out the question.

@dflak, I do love your approach; however allow me to contest the value of G1. If I'm reading this correctly it'd be 0.90 of an hour yet the max for any given interval is 15 minutes...

Here is a version that rounds the calculations out to the nearest quarter of an hour. However, you may wind up a bit short or over due to the rounding.

Originally Posted by Gregor y

If I'm reading this correctly it'd be 0.90 of an hour yet the max for any given interval is 15 minutes...

I think I was a bit hasty on this one, it is 0.90 of an hour, but 0.90 of an hour for 25 workers which is a total of 25*15 minutes available for the interval.

as you had it the interval would be overloaded at TotalHours*60/TotalPeople>15 or B34>81.75

The way I read the question was to convert the TotalHours to people and then spread those integer people as evenly as possible to each of the intervals staying >= 5 per interval used.