Reset Running Total

davin1130

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

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
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
30
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
30

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
707
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
707
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,106
Messages
5,622,772
Members
415,927
Latest member
vedasinternational

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