running countif a cell reaches a certain total

Hi Guys,

Im trying to make a spreadsheet to keep track of float variances for employees. The way the policy works is as follows: We keep track of an employees daily variances and keep a rolling balance (after 30 days old variances fall off the running total). If the the rolling balance hits $50 they receive an "occurrence". The tricky part is that once they hit the $50 mark and they receive their occurrence we subtract $50 from the rolling balance and start over. So say they have variances of $10, $23, $9 and $22 in a 30 day period they would have a rolling balance of $64. We would add an occurrence to their file and their rolling balance would become $14. Ive tried using a countif formula but Im running into issues when we reset the balance(Ie I add +1 to occurrence_level if variance >=50, but once we subtract the $50 this statement is no longer true and the occurrence level goes back to 0) Is there an easy way to keep a running count of occurrences even after we reset the balance? Im guessing I have to use a helper cell or macro, but the solution is escaping me!

Any help would be appreciated.


Ive edited my original post to include a sample excel spreadsheet. I have also included a picture of a flow chart to help people understand the end goal.

Thanks

Please read the yellow banner at the top of the page and atttach the file.

This proposal employs two helper columns (C:D) which may be moved and/or hidden for aesthetic purposes.
Paste the following into cell C4: =IF(B4="","",IF(SUM(C3,B4)>50,SUM(C3,B4)-50,SUM(C3,B4)))
Paste the following into cell D4: =IF(B4="","",SUM(B$4:B4)-C4)
Select C4:D4 and then drag the fill handle down to cell D26
Paste the following into cell G5: =INDEX(C:C,SUMPRODUCT(--(C:C<>""))+3,1)
Paste the following into cell F12: =INDEX(D:D,SUMPRODUCT(--(D:D<>""))+3,1)/50
Let us know if you have any questions.

Please upload a sample that demonstrates where the method doesn't work.
Please include the values that you expect so that we will have something against which to compare the output of our formulas/code.
Let us know if you have any questions.

See if this is correct:
1. Column C is populated using: =IF(B4="","",SUMIFS(B$4:B4,A$4:A4,">="&A4-30,B$4:B4,"<50"))
2. Column D is populated using: =IF(B4="","",IF(C4>50,C4-50*INT(C4/50),C4))
3. Column E is populated using: =IF(B4="","",C4-D4)
4. Rolling Balance formula is: =INDEX(D4:D36,SUMPRODUCT(--(D4:D36<>"")))
5. Rolling Number of Occurrence is: =MAX(E:E)/50
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.