SOLVED

New Contributor

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

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

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

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))),
)``````

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

Very tricky formula. Like it.

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

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 (New Contributor)
Solution

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

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.