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.