SOLVED

Sum until target, give a counter/ trigger once reached target, keep summing

Copper Contributor

Hey,

I've got demand data for 6 months, I would like to sum the data until the capacity of truck say 1000kg is reached. Once the column reaches the 1000 mark I would like to get a trigger/ break that puts 1 in the adjacent column. Since its 6 month data that varies  I need this automated for every 1000kg it passes in demand column.

Thanks

4 Replies

@smoiz 

This is a 365 solution that is probably total overkill for your requirement!

=LET(
    cumulative, SCAN(0,weight,LAMBDA(acc,w,IF(acc+w<=1000,acc+w,w))),
    newLoad,    IF(cumulative=weight,1,0),
    IF(VSTACK(DROP(newLoad,1),1), cumulative,"")
  )

image.png

Very tricky formula. Like it.

@smoiz 

For older versions: with data in A2 and down, enter the following formula in B2, then fill down:

 

=IF((SUM($A$1:$A1)<(MAX($B$1:$B1)+1)*1000)*(SUM($A$1:$A2)>=(MAX($B$1:$B1)+1)*1000),MAX($B$1:$B1)+1,"")

best response confirmed by smoiz (Copper Contributor)
Solution

@smoiz 

Used the following, worked for me since I had  trigger column running parallel to data. The input reference is to the capacity value at which I want a trigger.

 

=IF(SUM(G$7)<QUOTIENT(SUM(F$7:F8),Inputs!C$4),IF(SUM(F$7:F8)>Inputs!C$4,1,0),0)

 

Thanks for all the help, appreciate it. 

1 best response

Accepted Solutions
best response confirmed by smoiz (Copper Contributor)
Solution

@smoiz 

Used the following, worked for me since I had  trigger column running parallel to data. The input reference is to the capacity value at which I want a trigger.

 

=IF(SUM(G$7)<QUOTIENT(SUM(F$7:F8),Inputs!C$4),IF(SUM(F$7:F8)>Inputs!C$4,1,0),0)

 

Thanks for all the help, appreciate it. 

View solution in original post