Sum Until Value is Reached

[BIOKEKEC]

Need to SUM a number of boxes (column A) but only until one hour is reached in (column B)

 

[Chihiro]

I'd recommend you upload workbook with manually created expected result. Preferably with explanation of your logic.

I'm not a mind reader and not sure what your expected result would be from your data.

 

[BIOKEKEC]

My bad it was done in a hurry. Here is a file with an explanation, let me know if that helps or if there is something additional needed.

 

[Chihiro]

Can't think of way to this sort of calculation using formula off top of my head. Though @Peter Bartholomew might be able to show you solution using newer functions added in MS365 (LET, LAMBDA etc).

Do you have access to PowerQuery? Or is VBA solution acceptable?

FYI - There is one issue with your data set. Row 14, duration value has hidden date value. This should be corrected or you will get unexpected result.

 

[BIOKEKEC]

Unfortunately I do not have PowerQuery however I could probably acquire it same goes with implementing a VBA solution. I got in touch with a friend of mine and he came up with a solution as follows:
=LAMBDA(boxes,duration,cumul_value,cumul_range,IFS(duration>=1/24,boxes/duration/24,cumul_value+1/24-duration>=MAX(cumul_range),SUM(OFFSET(boxes,0,0,COUNTIF(cumul_range,">="&cumul_value),1))/SUM(OFFSET(duration,0,0,COUNTIF(cumul_range,">="&cumul_value),1))/24,TRUE,SUM(OFFSET(boxes,0,0,COUNTIF(cumul_range,"<="&cumul_value+1/24-duration)-COUNTIF(cumul_range,"<"&cumul_value),1))+OFFSET(boxes,COUNTIF(cumul_range,"<="&cumul_value+1/24-duration)-COUNTIF(cumul_range,"<"&cumul_value),0)/OFFSET(duration,COUNTIF(cumul_range,"<="&cumul_value+1/24-duration)-COUNTIF(cumul_range,"<"&cumul_value),0)*(1/24-SUM(OFFSET(duration,0,0,COUNTIF(cumul_range,"<="&cumul_value+1/24-duration)-COUNTIF(cumul_range,"<"&cumul_value),1)))))
I will take care of the hidden values thanks for the heads up.

 

[Chihiro]

Wait, if you have LAMBDA. Then you have PowerQuery (Data ribbon -> Get & Transform).

 

[BIOKEKEC]

True that my bad all of this is completely new and I do not really have a clue how it works hah