Got an Excel question. I throw myself before the Excel gods and pray for your merciful help!
Every time an employee sneezes, I put it on the chart below. If there are four or more sneezes in any seven-day period, then that is a single VIOLATION.
I need to know how many violations each employee has. (That is, I need help filling out Column C below. The screenshot below was done manually.)
Here are some ground rules:
- Each row indicates a sneeze, even if the third column is blank. (The word "VIOLATION" indicates the first sneeze in a group of four or more. The word "sneeze" indicates sneezes counting toward the VIOLATION.)
- A seven-day period includes the first and seventh days. E.g., Friday January 1st through Thursday January 7th is a seven-day period.
- Multiple sneezes on a single day count toward the "four or more" (See rows 17-23 below, where there are 8 sneezes falling within seven days, but 7 of those sneezes were on the same day.)
- A single VIOLATION is defined as any four or more sneezes falling within any seven-day period. E.g.: Four sneezes on a single day and none for the next six days is a single violation. E.g.: Ten sneezes a day for seven days is a single violation.
- Seven-day periods may have overlapping days. This is the big head-ache for me. See rows 31-36 below. Cara Cocci sneezed 6 times over an eight-day period from June 24th to July 1. This constitutes two seven-day periods, one starting on June 24 and another starting on June 25. If she had not sneezed on July 1, there would only be one VIOLATION because there would be only one seven-day period.
- The number of sneezes within a seven-day period is variable, but must always be four or more.
I would like to identify discreet seven-day periods that contain four or more sneezes. I need to list out that Arnie and Cara have two VIOLATIONS, and Fofi has one. It would ideal if I could re-create Column C exactly as shown: "VIOLATION" shows the first instance of a seven-day period containing four or more sneezes, and "sneeze" shows the supporting instances counting toward the "four or more."
I will edit my question as your comments seek clarifications. Thanks so much!
name,date,violation ARNIE AARON,5/6/2014 ARNIE AARON,5/13/2014,VIOLATION ARNIE AARON,5/14/2014,sneeze ARNIE AARON,5/16/2014,sneeze ARNIE AARON,5/19/2014,sneeze ARNIE AARON,5/19/2014,sneeze ARNIE AARON,5/21/2014 ARNIE AARON,6/9/2014 ARNIE AARON,8/29/2014 ARNIE AARON,10/3/2014 ARNIE AARON,10/10/2014 ARNIE AARON,10/14/2014 ARNIE AARON,12/31/2014 ARNIE AARON,1/20/2015 ARNIE AARON,1/23/2015 ARNIE AARON,3/13/2015,VIOLATION ARNIE AARON,3/13/2015,sneeze ARNIE AARON,3/13/2015,sneeze ARNIE AARON,3/13/2015,sneeze ARNIE AARON,3/13/2015,sneeze ARNIE AARON,3/13/2015,sneeze ARNIE AARON,3/13/2015,sneeze ARNIE AARON,3/16/2015,sneeze ARNIE AARON,4/16/2015 BART SEEBER,2/17/2014 BART SEEBER,2/17/2014 BART SEEBER,4/7/2014 BART SEEBER,4/30/2014 CARA COCCI,6/6/2014 CARA COCCI,6/24/2014,VIOLATION CARA COCCI,6/24/2014,sneeze CARA COCCI,6/25/2014,VIOLATION CARA COCCI,6/25/2014,sneeze CARA COCCI,6/25/2014,sneeze CARA COCCI,7/1/2014,sneeze DODI DEDDER,10/4/2013 DODI DEDDER,2/26/2014 DODI DEDDER,3/28/2014 EVIE EELANDE,10/31/2013 EVIE EELANDE,10/31/2013 EVIE EELANDE,1/12/2015 FOFI FAFARAZA,6/4/2013,VIOLATION FOFI FAFARAZA,6/4/2013,sneeze FOFI FAFARAZA,6/4/2013,sneeze FOFI FAFARAZA,6/4/2013,sneeze