Threshold Tracking

Hello,

I am uncertain which subforum to post this in. I have a spreadsheet that tracks dates of incidents, fictitious individuals involved in the incident, where they are located, and the incident type. Each incident type has a threshold criteria that, if met, must prompt a team meeting to review it. The team meeting can be an IDT meeting or Facility meeting based on which threshold criteria are met. I have listed the threshold criteria for each type of meeting in the Data Validation worksheet next to the corresponding incident type.

Based on the data that is entered, I need the spreadsheet to indicate in some way that a meeting is needed based on if the threshold criteria is met. For example, if the same individual has 3 Falls events in a 14-day period, the Falls threshold has been met and necessitates an IDT meeting. If the same individual has greater than 3 Falls in a 30-day period a Falls threshold has been met for a Facility meeting. Then the criteria starts over and 3 falls would be needed in a 14-day period for another threshold to be met for an IDT meeting to be had (and so on). Not sure if this should be accomplished by COUNTIFS, or Conditional Formatting, both?

The same spreadsheet must also track how many thresholds were met by individual in a calendar month. Maybe a pivot table?

Any assistance is greatly appreciated as this Excel tracking has become more complex than anticipated.

Hello Bernie, that is extremely close to what I need! However, the thresholds "reset" once they have been met. Take for example the "SIB - Minor Injury" IDT thresholds that are being met by "Oliver-Hancock, James". He met the IDT threshold on 8/5/2023, the threshold is "reset" and he won't meet another threshold until the Facility threshold on 8/9/2023 (which is displayed correctly). He would not meet the IDT threshold on 8/7, 8/8, and 8/9. This rule holds true for all of the incident types (IDT threshold, then Facility Threshold, then IDT, then Facility... and so on). I am not sure how Excel would know not to include the ones in between as they technically meet the numerical criteria.

Also, there were no values displayed in yellow in the file that I received. Which values are you referring to? If you need to specify a range I can take a look.

Thank you so much for your help on this. I wish I understood how the formula that you used arrived at the results, but I am just not there yet.

Oh, I see the values in yellow now, my mistake. With those, I think it would be safe to format Pica as 1 for IDT threshold, and Polypharmacy as N/A for the Facility threshold. It will just take the user to know that there are specific review tasks that need to be completed once they are met.

Hello Bernie,

Thanks again for all of your help. There seems to be some remaining issues. The criteria is not limited to calendar month. This means that "Oliver-Hancock, James" would meet the "SIB - Minor Injury" IDT threshold on 8/5 (3 within 14 days would include 7/31, 8/4, and 8/5), he would then meet Facility threshold on 8/9 (displayed correctly). The threshold would be "reset" and he would not meet the IDT threshold again until 3 more events afterward, yet it is showing that he's meeting a Facility threshold again on 9/11.

Similarly, "Bond, Melvina" would meet the "Falls" IDT threshold on 8/5 (3 within 14 days with 8/1, 8/4, and 8/5). She would then meet the Facility threshold on 8/7, but not the IDT threshold as she cannot meet both IDT and Facility thresholds as it must be one or the other.

I apologize that this is such a pain. I can't seem to figure out how to account for the nuances in our policy (i.e. alternating IDT and Facility threshold based on criteria, not meeting both at the same time, etc.).

Thanks again.

Hello Bernie,

I think that the alternating issue has been fixed. There is the data point on 8/9 for SIB - Minor Injury that shows both IDT and Facility threshold, but I think that the user could infer that the Facility threshold has been met and just have the one meeting.

There is an additional problem now though. The items that have a threshold of 1 are not showing that an IDT threshold has been met. For example, the data point on 8/1/2023 for Radford, Cameron for Chemical or Mechanical Restraint does not show that an IDT threshold has been met. This is the same for the data point on 8/10/2023 for Bell, Charlesetta for Choking.

Hello Bernie,

I am just circling back around because I have tried to resolve the issue that I noted above with no resolution. Would you care to give it another shot? I thank you for your assistance.