Counting Consecutive Occurences

[sweller]

I'm building an Excel sheet to calculate the fatigue index over a roster
of varying shifts and have now become stuck (again).

I need to calculate and score the effects of cumulative fatigue. Each
shift is assigned a letter based on its mid-shift time point Earlies (E),
Lates (L), Day (D), Nights (N) and Off (O).

So, we have a series of letters spread over a week and I need to count
the number of consecutive occurrences.

e.g.

Su Mo Tu We Th Fr Sa
O O O N N L L
O E D O O D D
D N N N N O O

etc. (for on average 36 weeks)

It's the counting up of consecutive occurrences that I'm stuck on, I can
(probably) get it to lookup a set of tables for the appropriate score.

These rules (FYI) are contained here
http://sweller.dynalias.org/fi/crr99254-ocr-p3031.pdf [50kb]

 

[Peo Sjoblom]

Using your example

=SUMPRODUCT(--(A2:F2=B2:G2))

adapt to fit your 36 weeks

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[sweller]

=SUMPRODUCT(--(A2:F2=B2:G2))

Could you explain how that works?

Will that count the number of instances a particular value (say "N") are
next to each other in a row of cells.?

Thanks.

 

[Peo Sjoblom]

Using your example

O O O N N L L
O E D O O D D
D N N N N O O


the first row would return 4
second 2 and third 4

Now to count a specific value

=SUMPRODUCT((A2:F2=B2:G2)*(A2:F2="N"))

note that the ranges must be of same size so you have
to change the range a bit



--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom