Reset Running Total

davin1130

New Member
Joined
Mar 15, 2019
Messages
38
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,119
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2393554636772232311405366
33974128631307295118115110466
4
5JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
6574086776980508865218865
757971837714680130881532110965
8
Sheet1
Cell Formulas
RangeFormula
A3A3=A2
B3:L3,B7:M7B3=IF(A3>100,B2,A3+B2)
 

davin1130

New Member
Joined
Mar 15, 2019
Messages
38
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

New Member
Joined
Mar 15, 2019
Messages
38

ADVERTISEMENT

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

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,119
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you upload sample data with expected result, using XL2BB?
I do not get your comment on one cell total.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,119
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2393554636772232311405366
3  128 130  118  104 
4
5JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
6574086776980508865218865
7  183 146 130 153 109 
Sheet1
Cell Formulas
RangeFormula
A3A3=A2
B3:L3,B7:M7B3=IF(A3>100,B2,A3+B2)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,623
Messages
5,838,422
Members
430,546
Latest member
CometOz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top