Sum a column until a specific number is reached

[DocDeb]

I would like to Sum a column until 50 is reached. Continue summing the same column again with the remainder from the previous cell if over 50 until 50 is reached again and keep going.
In an adjacent column insert consecutive numbers for each sum of 50.

 

[djclements]

Looks like a FIFO function could be used here. Try the following:

=LET(
    note, "the core elements of this method were taken from Peter Bartholomew's custom FIFO functions",
    FIFO, LAMBDA(inAmt,outAmt,
        LET(
            inRows, ROWS(inAmt) + 1,
            outRows, ROWS(outAmt) + 1,
            inRecords, HSTACK(EXPAND(SEQUENCE(inRows),, 2, ""), VSTACK(0, SCAN(0, inAmt, LAMBDA(a,v, a + v)))),
            outRecords, CHOOSECOLS(HSTACK(EXPAND(SEQUENCE(outRows),, 2, ""), VSTACK(0, SCAN(0, outAmt, LAMBDA(a,v, a + v)))), 2, 1, 3),
            unionAll, SORT(VSTACK(inRecords, outRecords), 3),
            inId, SCAN(1, CHOOSECOLS(unionAll, 1), LAMBDA(a,v, IF(v = "", a, v))),
            outId, SCAN(1, CHOOSECOLS(unionAll, 2), LAMBDA(a,v, IF(v = "", a, v))),
            amt, CHOOSECOLS(unionAll, 3),
            netAmt, VSTACK(DROP(amt, 1), TAKE(amt, -1)) - amt,
            FILTER(HSTACK(outId, netAmt), netAmt <> 0)
        )
    ),
    rng, A2:A100,
    arr, FIFO(rng, EXPAND(50, QUOTIENT(SUM(rng), 50),, 50)),
    one, CHOOSECOLS(arr, 1),
    add, one = VSTACK("", DROP(one, -1)),
    HSTACK(arr, SCAN(0, SEQUENCE(ROWS(arr)), LAMBDA(a,v, INDEX(arr, v, 2) + INDEX(add, v) * a)))
)

Adjust the rng reference (A2:A100) as needed. You could also define the FIFO function in Name Manager, if desired.

 

[DocDeb]

Maybe I've misunderstood or being too simplistic here, but isn't the answer just

=MOD(SUM(A$2:A2),50)

Book1
	A	B
1
2	1	1
3	4	5
4	6	11
5	22	33
6	11	44
7	6.02	0.02
8	3	3.02
9	5	8.02
10	11	19.02
11	99	18.02
12	13	31.02
13	6	37.02
14	5	42.02
15	23	15.02
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=MOD(SUM(A$2:A2),50)

Can you explain what you mean by

Thank you for this. It does work for decimals but not for values over 100.

As far as what I meant with, "In an adjacent column insert consecutive numbers for each sum of 50." I wanted the count of each occurance of 50 where the 50 is occuring.

Which I got to work in a crude way. I'm sure there's an easier way, but this works

Size	=A2+IF(B1>=50,MOD(B1,50),B1)	=LET(x, MOD(SUM(A$2:A2),50),IF(x=0,50,x))	=IF(AND($B2>=50,$C2<=50),"Vol","")	=IF(AND($B2>=50,$C2<=50),"Vol "&COUNTIF($D$2:D2,"VOL"),"")
0.5​	0.5​	0.5​
20​	20.5​	20.5​
20​	40.5​	40.5​
11​	51.5​	1.5​	Vol	Vol 1
5​	6.5​	6.5​

 

[DocDeb]

Once again:

Show sample. Both sample input data and expected results. Include such details as decimal parts (and how many digits in decimal part are expected in real values), large values (larger than single summation limit), zeros, negative numbers, non-numerical values, etc,

Whichever can be present in real data.

Otherwise we can misunderstand your needs. for instance @

myall_blues​

used a simpler version of formula proposed by me, which would result in 0 in case of sum being equal 50, while my proposition =MOD(SUM(A$2:A7)-1,50)+1 would result in 50 in such case. but the side effect is that for numbers with decimal parts yoy end up with 50.02.

Is this what you are referring to?

Column A could be .001 to over 100. No Negative numbers will be used.	Expected results are accurate as counting up to 50 and starting the counting over while counting the remainder until the next 50 is reached.	Expected results are accurate as counting up to 50 and starting the counting over while showing and counting the remainder until the next 50 is reached. But doesn't calculate properly with values over 100 in column A.	Shows the breaks where 50 is reached I would prefer not to need this coulmn	Shows the breaks where 50 is reached and counts the occurances of each break
Size	=A2+IF(B1>=50,MOD(B1,50),B1)	=LET(x, MOD(SUM(A$2:A2),50),IF(x=0,50,x))	=IF(AND($B2>=50,$C2<=50),"Vol","")	=IF(AND($B2>=50,$C2<=50),"Vol "&COUNTIF($D$2:D2,"VOL"),"")
0.5​	0.5​	0.5​
20​	20.5​	20.5​
20​	40.5​	40.5​
11​	51.5​	1.5​	Vol	Vol 1
5​	6.5​	6.5​
10​	16.5​	16.5​
5​	21.5​	21.5​
11​	32.5​	32.5​
8​	40.5​	40.5​
13​	53.5​	3.5​	Vol	Vol 2
6​	9.5​	9.5​
4​	13.5​	13.5​
23​	36.5​	36.5​
1​	37.5​	37.5​
4​	41.5​	41.5​
6​	47.5​	47.5​
22​	69.5​	19.5​	Vol	Vol 3
11​	30.5​	30.5​
5​	35.5​	35.5​
3​	38.5​	38.5​
5​	43.5​	43.5​
11​	54.5​	4.5​	Vol	Vol 4
8​	12.5​	12.5​
13​	25.5​	25.5​
6​	31.5​	31.5​
4​	35.5​	35.5​
23​	58.5​	8.5​	Vol	Vol 5
1​	9.5​	9.5​
4​	13.5​	13.5​
3​	16.5​	16.5​

 

[DocDeb]

Looks like a FIFO function could be used here. Try the following:

=LET(
    note, "the core elements of this method were taken from Peter Bartholomew's custom FIFO functions",
    FIFO, LAMBDA(inAmt,outAmt,
        LET(
            inRows, ROWS(inAmt) + 1,
            outRows, ROWS(outAmt) + 1,
            inRecords, HSTACK(EXPAND(SEQUENCE(inRows),, 2, ""), VSTACK(0, SCAN(0, inAmt, LAMBDA(a,v, a + v)))),
            outRecords, CHOOSECOLS(HSTACK(EXPAND(SEQUENCE(outRows),, 2, ""), VSTACK(0, SCAN(0, outAmt, LAMBDA(a,v, a + v)))), 2, 1, 3),
            unionAll, SORT(VSTACK(inRecords, outRecords), 3),
            inId, SCAN(1, CHOOSECOLS(unionAll, 1), LAMBDA(a,v, IF(v = "", a, v))),
            outId, SCAN(1, CHOOSECOLS(unionAll, 2), LAMBDA(a,v, IF(v = "", a, v))),
            amt, CHOOSECOLS(unionAll, 3),
            netAmt, VSTACK(DROP(amt, 1), TAKE(amt, -1)) - amt,
            FILTER(HSTACK(outId, netAmt), netAmt <> 0)
        )
    ),
    rng, A2:A100,
    arr, FIFO(rng, EXPAND(50, QUOTIENT(SUM(rng), 50),, 50)),
    one, CHOOSECOLS(arr, 1),
    add, one = VSTACK("", DROP(one, -1)),
    HSTACK(arr, SCAN(0, SEQUENCE(ROWS(arr)), LAMBDA(a,v, INDEX(arr, v, 2) + INDEX(add, v) * a)))
)

Adjust the rng reference (A2:A100) as needed. You could also define the FIFO function in Name Manager, if desired.

If I actually knew how to use this, maybe it would work?
I entered it into a cell and get #SPILL!

 

[myall_blues]

Is this what you are referring to?

When we say ‘show expected result’, we mean show us what you want to happen, not what you get from formulas we have provided.
For example

• When it crosses 50, should it show 50+a bit before it resets, or just show remainder e.g. 51 or 1?
• Likewise, if it gets to 50 exactly, show zero or 50?
• When you say it ’doesn’t work for values over 100 in column A’, what does that mean? Show us some values over 100 in A and what you’re expecting to happen.

 

[DocDeb]

When we say ‘show expected result’, we mean show us what you want to happen, not what you get from formulas we have provided.
For example

• When it crosses 50, should it show 50+a bit before it resets, or just show remainder e.g. 51 or 1?
• Likewise, if it gets to 50 exactly, show zero or 50?
• When you say it ’doesn’t work for values over 100 in column A’, what does that mean? Show us some values over 100 in A and what you’re expecting to happen.

• When it crosses 50, Column B should show 50+the remainder before it resets e.g. 51. Column C should show just the remainder e.g. 1
• If it gets to 50 exactly, show 50
• When Column A has a value over 100, Column C shows the value like it's over 50 not 100, so If I have a value of 101 in Column A, the remainder should be 51, Column C currently shows 1.

Expected result Example 1

A	B	C
.05	.05	.05

Expected result Example 2

A	B	C
99	99	49

Expected result Example 3

A	B	C
100	100	50

Expected result Example 4

A	B	C
101	101	51
2	3	3
49	52	2

Expected result Example 5

A	B	C
51.4	51.4	1.4
2	3.4	3.4
49	52.4	2.4
102	104.4	54.4
45.6	50	50

 

[Kaper]

Assuming row 1 is for headers, in B2:

=IF(ISNUMBER(C1),IF(C1>50,C1-50,C1),0)+A2

in C2:

=IF(B2>50,B2-50,B2)

and copy down

You may nest both formulas inside another if, so for both formulas in B2 and C2:

=IF(A2<>"" , formula_as_written_above_but_without_equal_sign , "")

 

[felixstraube]

.

 

[felixstraube]

I don't know if i got it right. Isn't the result you need the vol column?

If so, would this work? Just check column E (vol) (the others are just quick helper column to get to that result which can be compacted into a one cell formula)

Book1
	A	B	C	D	E
1	Size	Sum	Mod 50	Aux	Vol
2
3	50	50	0	1	Vol 1
4	2	52	2	0
5	49	101	1	1	Vol 2
6	102	203	3	2	Vol 3 to 4
7	45.6	248.6	48.6	0
8	150	398.6	48.6	3	Vol 5 to 7
9	45	443.6	43.6	1	Vol 8
10	15	458.6	8.6	1	Vol 9
Sheet1 (2)

Cell Formulas
Range	Formula
B3:B10	B3	=SUM($A$3:A3)
C3:C10	C3	=MOD(SUM($A$3:A3), 50)
D3	D3	=(B3-C3)/50
E3:E10	E3	=IF(D3, "Vol "&SUM($D$2:D2) + 1 & IF(D3>1, " to "&SUM($D$2:D2)+D3, ""), "")
D4:D10	D4	=(B4-C4)/50-SUM($D$3:D3)

 

[DocDeb]

=IF(B2>50,B2-50,B2)

Thank you.
Although, this is still not totally there.

0.5	0.5	0.5 (Good)
55	55.5	5.5 (Good)
101	106.5	56.5 (Good)
75	81.5	31.5 (This should be 81.5)

But, in Column C, I think I will just use =IF(B2-50<0,B2,B2-50) I wanted a more dynamic calculation, but this should do since Column B is working (as far as I can tell).