Reset Running Total

[davin1130]

I’m really struggling with this one … I have a spread sheet and Row 1 columns A-L contain the months. Row 2, I will be inputting numeric values for those months. Row 3 column A, I want to keep a running total for the values in row 2, however, the value will need to reset if the running total exceeds 100. The reset should begin with the next months value. So, if i exceed 100 in July, the reset value should begin with the value for August. Then in row 3 column B I want to put the month that the “reset” occurs from row 3 column A. Can anyone help. I know that this is similar to a previous post i put out, but I was given new direction for this one.

 

[GraH]

Since you start in column A it is difficult to make a formula that looks at previous result. You may try:
[A3]= A2
[B3] = IF(A3>100,B2,A3+B2) and drag across

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	39	35	54	63	67	72	23	23	11	40	53	66
3	39	74	128	63	130	72	95	118	11	51	104	66
4
5		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
6		57	40	86	77	69	80	50	88	65	21	88	65
7		57	97	183	77	146	80	130	88	153	21	109	65
8
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A2
B3:L3,B7:M7	B3	=IF(A3>100,B2,A3+B2)

 

[davin1130]

Thank you so much for your reply ... however i'm looking to only have one cell with the sum total (B3) .... if i enter values in the month row ... only cell B3 would change ... and when that value exceeds 100 ... B3 will start all over again.

 

[davin1130]

i'm ok with shifting the columns to make it work.

 

[davin1130]

You nailed it for the most part .... would only like to have one sum total cell

 

[GraH]

Can you upload sample data with expected result, using XL2BB?
I do not get your comment on one cell total.

 

[GraH]

Hmm, still under the reserve I understand what you want to do, if it's only about a visual effect, you can accomplish it with a custom format: [<100]"",0

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	39	35	54	63	67	72	23	23	11	40	53	66
3			128		130			118			104
4
5		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
6		57	40	86	77	69	80	50	88	65	21	88	65
7				183		146		130		153		109
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A2
B3:L3,B7:M7	B3	=IF(A3>100,B2,A3+B2)