Surveillance data- finding counts of 2 or more cases in past 14 days

I am working with school health data (ie: new cases of people infected with a virus) and I have the data stored in line lists that generally have the following columns:

Name Student or Staff Grade/Classroom Home City Date of onset


What I'm trying to do is figure out the formula or commands that will allow me to count how many classrooms have had ≥ 2 positive cases in the past 14 days (rolling). I have very limited excel experience so apologies in advance if this is a simple fix. Also, if this is not enough information or confusing, please let me know. Thanks!

Hi Clari and welcome to the forum,

In Excel Pivot tables there is a way to do a "Difference of Previous" that may work for your problem. If you create a Pivot with your data and show the values as a difference from the previous and then pick those out that are 2 or less you might have your needed answer. See the attached where I've done this with your example data.
Same Class Previous 2 days Pivot.xlsx

See if this formula entered in F2 and copied down works. Without meaningful data (rolling) though I am not sure if this does what you want.

Formula:

Please Login or Register  to view this content.

Right now they all return FALSE as none of your data qualify.

I guess the best way to start for me is can you tell us what results you expect to see given the workbook you posted? As it is there are no instances with 2 or more cases in a 14 day period. Hence the all FALSE results.

From there perhaps someone can back engineer sufficient understanding to create a formula. As it is I have more than one interpretation of your request in mind. None of them can make sense (to me) given the upload and what you request in post #1.

Let's see if I've interpreted correctly this time.

I had to create a copy of the original data and sort it on the date ascending.

In column G this formula:

Formula:

Please Login or Register  to view this content.