Excel attendance register 3

[jonsi]

I have a simple Excel attendance register. Each person has a worksheet within a workbook. Each worksheet shows 1 week per row with Sunday - Saturday in columns B-H.

If someone is Present the cell entry is 'P'
If someone is Absent the cell entry is 'A'
If someone is Sick the cell entry is 'S'

I can do the COUNTIF functions etc. but, the problem I have comes where there is more than one 'A' in any one week. If a person is absent on the Monday it counts as one period of absence, if they are absent on the Tuesday also it still only counts as one period of absence. If they were absent on the Monday, came into work on the Tuesday and were absent again on the Wednesday that would count as two periods of absence. It's a sort of 'Bradford Factor' thing I think.

A normal week's record should look like -PPPPP-, the example above would look like -APAPP-.

How do I get Excel to recognise consecutive cell inputs of A as one period of absence (-AAAPP-) but single instances (-APAPA-) as three?

thanks
Jonsi

 

[SkipVought]

hi,

Assuming that your codes are in B3:F3
[tt]
B C D E F

a a a p p
[/tt]
then your formula in B2
[tt]
B2: =IF(B3="A",IF(A3="A",0,1),0)
[/tt]

the results for absences
[tt]
B C D E F
1 0 0 0 0
a a a p p
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jonsi]

worked like a charm Skip, many thanks

(now why didn't I think of looking backwards like that?)

Jonsi (who has been away from this site for far too long)

 

[SkipVought]

Welcome back!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

If you would like a formula to count absences without need for the auxiliary row of IF formulas, then consider:
=SUMPRODUCT((C2:G2="A")*(D2:H2<>"A"))

The SUMPRODUCT formula is testing two ranges offset by one day. The first test looks for an absence. The second test looks for anything except an absence (i.e. P or -) on the following day. SUMPRODUCT only adds to the count when both criteria are satisfied.

If you have Excel 2007 or later, you can use COUNTIFS as an alternative:
=COUNTIFS(C2:G2,"A",D2:H2,"<>A")

Brad

 

[jonsi]

Thanks Brad, that too works like a charm and is very tidy
Jonsi

 

[SkipVought]

Brad,

Very good! I would have never thought of that approch! But it makes sense. Nice call!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}