2

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:

  1. 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.)
  2. A seven-day period includes the first and seventh days. E.g., Friday January 1st through Thursday January 7th is a seven-day period.
  3. 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.)
  4. 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.
  5. 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.
  6. 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!

enter image description here

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
  • What have you tried already? Why is violation labelled for the first occurrence in the series and not when it actually occurs? – Sun Jun 28 '16 at 18:21
  • @Sun - Hi, thanks for helping. I tried formula where name is same as name above, and date minus date above is 6 or less, then say "sneeze." I also tried pivot table, with the dates as columns, grouped by 7 days, but that does not cover overlapping 7-day periods. You are correct that VIOLATION is labelled for the first occurrence even though it is the 4th (or 5th or 6th...) event that defines the VIOLATION. That's because later on down the road I need to be able to cite not only how many violations there are but when they "started." I appreciate your help and am happy to add clarifications. – Xplodotron Jun 28 '16 at 18:54
  • 2
    It must really suck to have allergies and work at your company. Just sayin'. – fixer1234 Jun 28 '16 at 20:59
  • @fixer1234 - Lol. I supervise the salad bar inside a dust-free chip manufacturing plant. There's a funeral home there also, so we need to be really really quiet. – Xplodotron Jun 28 '16 at 21:08
  • Why do column C now have data? I'm confused why some employees have dates, but no sneeze next to them. What is the significance of that row? – Sun Jun 30 '16 at 19:14
0

To solve your problem you need to use a bunch of IF and COUNTIFS function with clever mixture of objective & relative references.

My formula follows reworded logic of your question (results will be the same):

  1. If there is already a VIOLATION for the given person at the given date, then actual row will contain sneeze
  2. ELSE, if there are at least 3 rows listed for the given person within seven days (fourth instance is always the current row) then return VIOLATION
  3. ELSE, if there is a VIOLATION for the given person within the previous seven days, then return sneeze
  4. ELSE keep cell blank

=IF(COUNTIFS($A$1:A1,A2,$B$1:B1,B2,$C$1:C1,"VIOLATION")>0,"sneeze",IF(COUNTIFS(A3:$A$46,A2,B3:$B$46,"<"&B2+7)>=3,"VIOLATION",IF(COUNTIFS($A$1:A1,A2,$B$1:B1,">"&B2-7,$C$1:C1,"VIOLATION")>0,"sneeze","")))

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.