Forum Discussion

smoiz's avatar
smoiz
Copper Contributor
Dec 14, 2022
Solved

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

excel
  • smoiz's avatar
    smoiz
    Dec 15, 2022

    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. 

4 Replies

  • smoiz's avatar
    smoiz
    Copper Contributor
    Dec 15, 2022

    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. 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 14, 2022

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

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 14, 2022

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

Resources