counting penalties in an array

[Rjot1234]

Folks,
I have a problem in which a have a number of assets are going to a number of locations. When the number of cumulative number of assets exceeds the number of assets available they are assessed a penalty associated with the previous location they serviced. In the attached example the first instance this happens is Location 5 (cumulative number of assets is 40, they are 30 available - therefore the entity should be charge 5 units (location 5 + 2 penalty associated from the last location in this case 3).

vr
Ricexample.xlsx

[canapone]

Hi,

an attempt: in E8 to be copied to the right.

=IF(MAX(E4:E5)>30,E7+LOOKUP(2,1/($D7:D7),$D7:D7),0)

Example: under location 8, formula returns 9 (5+4)

Regards