Calculating cumulative values and reducing the total sum each day

07/29/21
Answered - Pending Review

Hi, I have a Safety Violation Sheet that uses Form Entry to record violations and assign a measured Probation Period in days. If there are multiple entries for an individual, the probation days should accumulate, at the same time the value reduces by (1) for each day that passes without incident.

I have (1) sheet that acts as a Name database for the company, and a column on that sheet that uses: =SUMIF({Safety Violation (Responses) Range 1}, [EMPLOYEE NAME]@row, {Safety Violation (Responses) Range 2}) to show the cumulative Probation Days accrued.

I also have a column on my Safety Violation Sheet that looks up the amount of days per violation entered: =VLOOKUP([Severity of Violation]@row, {SAFETY VIOLATION WORKSHEET Range 2}, 2, false)

and another column that reduces the Probation Period by (1) for each day that passes: =SUM(TODAY() - [email protected]) and =SUM([PROBATION END (HELPER)]@row - [PROBATION DAYS (HELPER)]@row)

Unfortunately the way I have the formula it reduces (1) day for each entry, so if John has (3) violations, (3) days are removed for each day that passes, and it should only reduce by (1) day for whatever has accumulated.

It seems I may need to move my Reduce by (1) day formula to my Name Database sheet, next to the the SUMIFS formula, but I'm having trouble with what that would look like.

I feel like there should be an easier way to do this...


Any help would be appreciated

Thanks!

Answers

  • Hello @Shawn Leahy

    Hope all is well! Thank you for the description! May you please provide us screenshots (please block out any sensitive data) so we can try to replicate your setup to further test on this. Thank you~


    Cheers,

    Krissia

  • Shawn LeahyShawn Leahy ✭✭✭✭✭

    Hi Krissia,

    Sure doing well and hope the same for you! Thanks for your response, hope you can help.

    Here are a few screenshots, I cropped, hid, and redacted but hopefully left you enough to see what I am doing. I overlaid the formulas I am using in the columns they are in. Let me know if you need to see anything else.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    To pull the most recent date for someone, you are going to want something along the lines of...

    =DATEONLY(MAX(COLLECT({Source Data Timestamp Column}, {Source Data Name Column}, @cell = [Violator Name]@row)))


    Subtracting that date from TODAY will give you how many days have passed since the last incident for that person.

    =TODAY() - DATEONLY(MAX(COLLECT({Source Data Timestamp Column}, {Source Data Name Column}, @cell = [Violator Name]@row)))

    thinkspi.com

  • Shawn LeahyShawn Leahy ✭✭✭✭✭

    Hi Paul,

    Thanks for your time on this!

    Those suggestions would work fine if we were only looking at the current Violation. Our program is cumulative to a point where a specific number results in Disciplinary Action, it does not reset with the newest violation.

    What I am hoping for is to keep track of how many Probation Days that a Violator has accumulated and subtract days go by without incident, so:

    Steve had a violation that cost him 182 Probation Days on June 1st, and another incident on July 13th that cost him 182 Probation Days. How many days does he have left as of August 4th?

    (43) days passed between June 1 & July 13, and (20) days have passed between July 13th & August 4th

    182 - 43 + 182 - 20 = 301 Probation Days Remaining as of August 4th.

    The problem I am having is I don't have a method to Only subtract (1) Probation Day from someone that has multiple violations. It seems like I might need to prioritize the most recent Violation for a specific person and reduce that by (1) every day, but then somehow freeze all the others so they don't count down until the most recent zeros out... just not sure what that would look like.

    Hope this is making sense.

    Appreciate any help you can offer.

    Thanks!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    My apologies. I thought you had the other parts figured out all except for how many days since the last violation. I am going to have to get back to you when I have a little more time to work on this.

    thinkspi.com

  • Shawn LeahyShawn Leahy ✭✭✭✭✭

    No worries, appreciate your time!

Sign In or Register to comment.