Number of instances

[Guest]

I have a spread sheet that records sickness levels, what I'm after is a
formula or maybe even a macro that will return the about of sick instances
plus amount of days. The problem is that it needs to somehow have a bit that
due to shift patterns of 4 on 4 off reconises that if the gap is more than 5
days part from the last instance then it is a new instance if it is less than
5 then it's the same instance

Example

At the start of D3 running right out are the days of the month starting from
Jan 01 to Dec 31st
In A4 down to A100 are a list of names
If i use January as an example i need the answer to come to 3 instances and
a total of 12 days off

Cell
D4 = s
E5 = s
F6 = s
G7 = blank
H8 = Blank
I9 = blank
J10 = Blank
K11 = Blank
L12 = Blank
M13 = Blank
N14 = Blank
O15 = s
P16 = Blank
Q17 = Blank
R18 = Blank
S19 = Blank
T20 = s
U21 = s
V22 = s
W23 = s
X24 = blank
Y25 = blank
Z26 = blank
AA27 = blank
AB28 = s
AC29 = s
AD30 = s
AE31 = s

Hope this makes sense

 

[Pete_UK]

Your example data seems to be in a diagonal pattern going down and
across the sheet. Wouldn't all the data for one member of staff be on
the same line? And wouldn't you want to have the sickness analysis
carried out for each member of staff? By my reckoning there are 4
instances of sickness in your example data.

A bit more clarification, please.

Pete

 

[Guest]

Given an interpretation of the rule you had in your previous posting (sse
below) , I colud argue there are only 2 instances.

Q15 is within 5 of T20 and W23 is also within 5 of AB28: 1 instance?

You need to spell out much more clearly what the rule(s) is(are).

 

[Guest]

Pete,
Data is one row per person: Op mis-posted!

Your example data seems to be in a diagonal pattern going down and
across the sheet. Wouldn't all the data for one member of staff be on
the same line? And wouldn't you want to have the sickness analysis
carried out for each member of staff? By my reckoning there are 4
instances of sickness in your example data.

A bit more clarification, please.

Pete

 

[Pete_UK]

Thanks Toppers.

To Louis: I answered a similar query in October 2006 - the thread was
titled "Occasions in a sickness record" and it was posted in
public.excel. Perhaps this might be of use to you if you search the
archives for it.

Hope this helps.

Pete