• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum Until Value is Reached

BIOKEKEC

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

Attachments

  • Test.xlsx
    9.2 KB · Views: 4
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.
 
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.
 

Attachments

  • Test.xlsx
    10.8 KB · Views: 9
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.
 
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.
 
Back
Top