Formula for allocating where value is dependent on values allocated before and after

I have run across a difficult to solve formula. The basic problem is employees are guaranteed 15 hours of overtime weekly, three hours each day before they run into double pay overtime for the 4th hour. Whether they work one hour of OT or 15, they are paid for 15 for the week, and up to 3 hours needs to be allocated per day, unless of course one day they work more than 3 hours - anything above 3 hours per day counts toward the guarantee of 15 hours for the week. (And of course they are paid above 15 hours, and the guarantee is satisfied.) I have written a formula to allocate hours from the remaining unworked guarantee to the days, however in situations where a prior day has fewer hours actually worked than a later day in the week, the formula under allocates to the week and so we don't meet our 15 hour guarantee. The scenario is laid out in the attached spreadsheet. The formula I need appears to be some sort of iterative calculation dependent on all the allocated days, but I am stumped. Any thoughts would be much appreciated!

Hi, thanks so much, but each daily guarantee of 3 hours summing to 15 is a hard entry and does not change from 15 total guaranteed, so I think I am good with the formula in H2 -- it's the formulas for B8:F8 that seem to be the problem. B8 is currently:
IF(B$4<0,0,IF(B$4>(B9/B7),(B9/B7),B$4))

In that scenario B4 represents the hours remaining to fill up the 3 hours guaranteed that day, B9 is the running balance of guarantee remaining, and B7 is the days remaining using the guarantee.

Any thoughts on a formula beginning in B2 that would allocate the hours for today toward the unmet guarantee that take into account the hours worked today already, the remaining amount available to fill up the 3 hours guarantee for the day, and the hours worked in both the days preceding and following the day?

Thanks for clarifying and apologies if I have been unclear - it's fairly complex and I tried to err to keeping it simple, but probably I did not provide enough detail.

If they worked 3 days they would get 9 hours guaranteed, but for the purposes of this example let's say they work every day.

A8 is hours to be allocated from the remaining guarantee unused forthe week. In other words, what maximum portion of the unused guarantee of 8.5 hours can be allocated to this day, given the actual hours worked today and a max of 3 hours available to each day (lest we go into Double Time above 3 hours).

A9 is the Running Balance of the remaining unused guarantee after we subtract what we allocate to this day, i.e. what's remaining after today and available for further days.

Basically the formula in Row B should allocate the maximum hours from the unused guarantee to each day, based on each day's actual hours worked and the week's remaining guarantee. Allocating the guarantee per day is necessary because it gets coded to whatever project was worked on that day, which can vary from day to day.

The formula now works in most instances but when Tuesday has a higher actual hours worked total of 2.5 hours and that is higher than Monday's of 1 horu actually worked, the current formula under-allocates hours to Monday. It should allocate 2 hours, but instead allocates only 1.3. Oddly when Monday has total hours actually worked of 1.5 hours or higher, the formula works. So, there is some mathematical relationship I am missing. I think it is probably some sort of iterative calculation, but perhaps there is a simpler solution?

Thanks again. The calculations being set to manual was inadvertent -- sorry.

The formula in B9 references H4 because I am using B9 as the anchor cell (starting point) for how many guaranteed hours are available to allocate to the days. I was tying the starting point to the remaining guarantee for the week, because in some cases the amount allocated to a day could be less than hours in the day's 3 hour guarnatee remaining that have not been worked for that day. For instance, if the employee worked, say, 13 hours of OT Tuesday-Thursday (i.e. one day he or she worked one hour into double time, i.e. 4 hours of OT, which counts toward the guarantee), then even though only one hour of OT was actually worked Monday (leaving 2 potentially available), then only one hour would allocate to Monday because that is all that is remaining for the weeks guarantee -- the 13 hours Tues-Fri plus Monday's actual hour of OT worked plus the remaining 1 hour in the guarantee. That is where nature of the one day being conditional on every other day factors in. This could definitely be the wrong approach, but somehow I need to account for the allocation of each day being conditional on both the hours actually worked that day, the total amount available to allocate for the week, and the max amount that can be allocated to any day (i.e. 3 hours) without hitting double-time OT.

Incorporating your tweak of "less than or equal to 0", the formula in B8 is: IF(B$4<=0,0,IF(B$4>(B9/B7),(B9/B7),B$4)). My verbal recapitaluation of that formula is:

"If the remaining capacity of today's allocable guaranteed hours (here 2) is less than or equal to 0, then 0, otherwise if the remaining capacity of today's allocable guaranteed hours (2) is greater than the pro rata portion of the week's total allocable hours (i.e 1.3 --i.e. the running balance of 6.5 divided by days remaining to which the guarantee will be spread of 5), then use the pro rata portion of the week's total allocable hours (here 1.3), otherwise use the remaining capacity of today's allocable guaranteed hours (here 2)." And of course in this situation it should be using the 2 hours instead of 1.3, so the formula has broken down. But, if you change the respective portion of the formula from IF(B$4>(B9/B7) to IF(B$4<(B9/B7) then the formula breaks down in the opposite direction, i.e. in situations where you work more than (vs. less than) 1.5 hours Monday while working 2.5 hours Tuesday. So, I am not getting something either in the mathematical proporty or possibly there is some sort of iterative calculation that needs to happen?

My theory is that because each day's allocable total of guaranteed OT is tied to the total available for the week, keeping mind the remaining max capacity for that day, then you have essentially have to take into account how many days are left in the week from any point in time you are currently evaluating. And the formula does seem to work except when a later day's actual OT is greater than 1.5 and the previous day's is less than 1.5. So, I am stumped as to how to write a formula that accounts for the conditional nature but does not break down if the Monday has a total greater or less than 1.5 hours of actual OT when Tuesday is above 1.5...

Hopefully I have been clear, but of course ask away if I have not been and in any case I truly appreciate the help!