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.

 

[Kaper]

It's a pitty you've not shown sample, because i think your description could be one of 2 options. Let's try them both.
Assuming your data starts in cell A2 (A1 holds header) you may use:

=A2+IF(B1>=50,MOD(B1,50),B1)

or

=MOD(SUM(A$2:A7)-1,50)+1

depending whether sum just after reaching 50 shall be shown as fifty-something or just as this what is above 50

Or may be you are asking about some other possibility?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data along with expected results, as it's not clear what you want.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[DocDeb]

It's a pitty you've not shown sample, because i think your description could be one of 2 options. Let's try them both.
Assuming your data starts in cell A2 (A1 holds header) you may use:

=A2+IF(B1>=50,MOD(B1,50),B1)

or

=MOD(SUM(A$2:A7)-1,50)+1

depending whether sum just after reaching 50 shall be shown as fifty-something or just as this what is above 50

Or may be you are asking about some other possibility?

That's what I needed! Thank you!

 

[DocDeb]

Sorry, I do have a question: Both solutions work great, except =MOD(SUM(A$2:A7)-1,50)+1. If the number in the first column is not a whole number, =MOD(SUM(A$2:A7)-1,50)+1 shows the fraction plus 50.
How can I fix this?
Example:

	=A2+IF(B1>=50,MOD(B1,50),B1)	=MOD(SUM(A$2:A7)-1,50)+1​
0.02	0.02	50.02
4	4.02	4.02
6	10.02	10.02
20	30.02	30.02

 

[DocDeb]

And I now find if the first number in A2 is over 100, '=MOD(SUM(A$2:A7)-1,50)+1' only shows the remainder of 50.
Can this be fixed?
Example 1

	=A2+IF(B1>=50,MOD(B1,50),B1)	=MOD(SUM(A$2:A7)-1,50)+1​
99	99	49

Example 2

	=A2+IF(B1>=50,MOD(B1,50),B1)	=MOD(SUM(A$2:A7)-1,50)+1​
100	100	50

Example 3

	=A2+IF(B1>=50,MOD(B1,50),B1)	=MOD(SUM(A$2:A7)-1,50)+1​
101	101	1

 

[myall_blues]

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

In an adjacent column insert consecutive numbers for each sum of 50.

 

[Kaper]

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.

 

[myall_blues]

@Kaper agreed that example data is required, but the problem of showing 50 instead of zero can be solved as

=LET(x, MOD(SUM(A$2:A2),50),IF(x=0,50,x))

 

[Kaper]

but that anyway does not address the issue raised in post #6 with values like more than 50 or may be even 2137 or so