Counting consecutive cells in a row

**her.rockstar** · 04-04-2013, 10:19 AM

I have a table that lists observations completed over the course of the year.
If someone goes three consecutive months without completing an observation, I would like an "R?" to appear to the right of the table. (this will remind me to retire the person)

One issue I can see is the months ahead of now (May-Dec) that are also empty may cause problems.
I've attached the dummy sheet

dummy7.xls

**martindwilson** · 04-04-2013, 11:24 AM

here is one way but i may have overthought it!

**her.rockstar** · 04-04-2013, 11:37 AM

Wow, it's almost exactly what I'm looking for, the issue I have is this:

Currently the cut off date appears to be June, which I believe you have set there because it's three months (including April, May, June). Right? At this point in time, the only way it should show R is if "JAN FEB MAR APR" are blank, or if "FEB MAR APR" are blank. Anything after April should have no bearing, until.... next month it should shift up, so "JAN FEB MAR APR" blank will show R, and/or "FEB MAR APR" and/or "MAR APR MAY", etc. and so on. I know it's confusing.

Does that make sense? Really great work though!

**martindwilson** · 04-04-2013, 12:18 PM

nope just put whats in o3 in o1 instead i just used june to test and since april hasnt ended should it be included? if it is then use
=eomonth(TODAY(),1)+1 instead of =eomonth(TODAY(),-1)+1 the formula checks for any period of 3 consecutive months prior to date in O1

**her.rockstar** · 04-04-2013, 12:25 PM

Awesome!. .Thanks for your help!

**her.rockstar** · 04-04-2013, 12:33 PM

Okay, I understand what you did there, it works like you're saying.

HOWEVER, I noticed that if I leave JAN FEB MAR blank and then put a 1 in APR, it still shows retired. I suppose I didn't mention this in my main post, so I'm very sorry. IF the person does an observation after 3 month period of no activity, the R should disappear, as they'd be considered active again.