Count the number of consecutive occurrences

**timmycl_7** · 11-18-2008, 10:27 AM

Hi,

I have an excel spreadsheet to record employee holiday and sickness figures.

It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.

One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.

Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.

Any help would be much appreciated!

Thanks

**NBVC** · 11-18-2008, 10:39 AM

Try:

Please Login or Register  to view this content.

adjust ranges to suit... and confirm with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around the formula.

**Ron Coderre** · 11-18-2008, 10:43 AM

With
A1: Dates
A2:A50 contains dates
B1 contains an employee name
B2:B50 contains workday status values: IN, ABSENT, OFF, whatever

This ARRAY FORMULA, committed with CTRL SHIFT ENTER
(instead of just ENTER) counts the maximum consecutive
occurrences of "ABSENT"

Please Login or Register  to view this content.

Is that something you can work with?

**Cheeky Charlie** · 11-18-2008, 10:50 AM

Shamelessly poached:
=MAX(FREQUENCY(IF(A1:A10="ABSENT",ROW(A1:A10)),IF(A1:A10<>"ABSENT",ROW(A1:A10))))
Enter as array formula (Ctrl + Shift + Enter)

http://www.ozgrid.com/forum/showthread.php?t=71645
Domenic's post (#5)

CC

**timmycl_7** · 11-18-2008, 11:19 AM

Hi Guys,

Thanks for the quick response.

I used NBVC's formula which has worked.

Much appreciated.

**timmycl_7** · 11-18-2008, 11:43 AM

Hi,

As I mentioned I have used NBVC's formula and it works to what I described, however I have realise that I had an oversight when describing what I required.

An employee could be off 28 calendar days but only 16 of those days would be counted as working days because they work a 4 day week. If I was to enter the word "OFF" could the formula be amended to still count absent days consecuatively if the work "OFF occurs in between it?

Sorry, thanks in advance!

**Ron Coderre** · 11-18-2008, 12:14 PM

Try this ARRAY FORMULA variation:

Please Login or Register  to view this content.

Is that something you can work with?

**NBVC** · 11-18-2008, 02:11 PM

Originally Posted by timmycl_7

Hi,

As I mentioned I have used NBVC's formula and it works to what I described, however I have realise that I had an oversight when describing what I required.

An employee could be off 28 calendar days but only 16 of those days would be counted as working days because they work a 4 day week. If I was to enter the word "OFF" could the formula be amended to still count absent days consecuatively if the work "OFF occurs in between it?

Sorry, thanks in advance!

Try:

Please Login or Register  to view this content.

adjusting ranges again and confirming with CTRL+SHIFT+ENTER

**timmycl_7** · 11-19-2008, 05:28 AM

NBVC,

Thanks for your help, I think I have got the formula working now. I used the first formula you gave me making some slight adjustments.

Thanks

**Rennier** · 02-16-2011, 06:27 AM

Hi there!

I don't know if you are still at this address but here's trying.

Could you please forward me your spreadsheet and maybe just an explanation on how you use/d it? I have been tasked to look at absenteeism and using the Bradford Index although we are looking at a period of 26 weeks in which the absenteeism takes place and it seems what you did will help me a great deal.

Thanking you in advance,
Rennier
South Africa

**svako** · 05-03-2012, 12:44 PM

morning,

I have a question that is somewhat related to the above. I have values in several columns, each value can only occur once per row. I'm trying to find out two things:
1/ what is the longest consecutive occurrence by a particular value, say '23' within a certain date range;
2/ what are the top 3 (or 5) consecutive occurrences by any value within the same date range.

In other words, what I need to find out is this: 'the number 23 has occurred 7 times consecutively from 1-Mar-09 through whatever (dates are in the column A). And the longest consecutive occurrences have been made by numbers 'x', which has occurred so many times, from such and such date through such and such date. The next number that did this, was 'y', which occurred some many times, etc, etc.

any help is appreciated,

thanks,

L

**icestationzbra** · 05-03-2012, 01:09 PM

svako - do not exhume old ghost, err, posts... currently you can create a new thread at no charge, so why not make use of that opportunity and have a brand spanking new thread for yourself, instead of a hand-me-down!!!

**arlu1201** · 05-03-2012, 02:04 PM

Svako,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.