I am currently working with a very large spreadsheet (650000 rows) and need to use a formula to search a column for the most recently occurring positive number.

To give some context, the spreadsheet contains flow data from every minute from the passed 16 months. If the net flow is positive it means the storage tank was filling (inflow), if the net flow is negative it means the pumps were activated and the tank was draining. However, while the tank is draining there is still technically an inflow occurring and my goal is to split these up. My estimate is based on the assumption that whichever inflow was occuring just prior to the tank from draining is constantly occurring throughout the outflow time.

I currently have a column returning me the value I want, but it is with a lookup function which excel cannot handle at this size:

=IF(I16="No Change", 0, IF(I16="In", 0, IF(I16="Out", ABS(F16+(LOOKUP(2,1/($F$4:F15>0),$F$4:F15))), "Error")))

The I column contains if statements that check if the net flow is positive or negative, and the F column contains said net flow values.

Is there a more compact way of performing this task? I have attempted some Index:Match functions with no luck.

Thanks,

Jeremy