Reset running total and provide date.

[davin1130]

Need help please … i have a spread sheet with 4 columns. Column A is a date; Column B is a numeric value; Column C is the running sum of the values in column B (but i'd like to reset the value once the sum reaches the value of 5000); and Column D is the date from column A once the sum of column C reaches the value of 5000. Look for help in the logic for columns C & D. Thank you in advance !!

 

[mrshl9898]

Try this:

C2 =SUM(B$2:B2)-(INT(SUM(B$2:B2)/5000)*5000)
D2 =IF(C3<C2,A2,"")

 

[davin1130]

Thank you for the reply … wondering if the capability is there to keep the true sum value in column "C" when it goes over 5000 and in the following cell the reset starts … for example if column "C10" sums to 5150 it would stay at 5150 and then column "C11" would start fresh.