Complicated formula - Need Help

Hello all,

I'm not sure if this is complicated or not (I'm a noob), but would appreciate any help I can get with achieving this. Will try my best to explain it.

The grey bars shows a 7 day rolling period. There is a limit of 24 for any 7 days and out of the 24 there is a limit of 20 for logistics.

How do I get the remaining hours column for logistics to calculate correctly taking into consideration the 20hr limit which will also be affected by the 24 hr limit?

Currently it is just being calculated based on the 20hr limit for logistics. As you can see even though I only have 1 hr remaining from the 24hr limit, the logistics column is still showing 2.

Not sure if I explained that correctly or made much sense

Capture.JPG

Hello,
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

I agree a sample is better but =min(your current formula, column u) something like min(20-sum(f4:l4),u4)

Should I just upload the current excel file? I did not lay out any information elsewhere. I just created this table and add in the information then try to formulate accordingly.

This formula did help a bit but it keeps throwing up the same value for all the other cells, even though I changed the cell number accordingly in the formula.

Thanks for responding everyone. I have manage to figure it out using the IF function. Works like a dream now