Analysing attendance

[gjb5]

Hello.

I have a database of individuals attending football coaching that I run (100 people in rows 2-101). This includes their start date (Column A) and then a column (Column B-Z) for every week the project runs. A ‘1’ is placed in attendance columns for each week someone attends. A ‘2’ is entered for non attendance.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’d like to analyse the database to understand how long (in weeks) people are absent and whether they come back. Can anyone help?

Thanks in advance
gjb5

 

[kpark91]

If you want to count their non attendance try inserting this in your AA2 and copy down

=COUNTIF(B2:Z2, "2")

I'm not too sure if this is what you want because you didn't really specify which data to exactly show..

 

[sijpie]

So you want more than just count the '2's in the sequence - you want to ocunt the consecutive '2's or something.

What could be usefull is count the last consecutive absences. So an attendence of
1 2 1 2 1 2 1 2
would get a score of 1, indicating the person is not off long,
and

1 2 1 1 1 2 1 1 would also score 1.

However
1 1 1 2 2 1 2 2 1 2 2 2 2
would score 4, indicating hasn't been here for four weeks, mmmh, is he coming back or reliable?
Also
1 2 1 1 2 2 2 2 1
would score a 4 indicating unreliable pattern or you could subtract the last attendece from it giving a score of 3.

What do you think?

 

[gjb5]

Thank you for the replies.

I'm interested in the length of last consecutive absence. How can I get Excel to return a value (6 in the example below)that counts the number of weeks absent?

1 2 2 1 1 1 1 1 2 2 2 2 2 2

Understanding if people return after an absense is also something I'm looking to test. Any ideas using the example below?

1 2 2 1 1 1 1 1 2 2 2 2 2 2 1

Thanks
gjb5

 

[sijpie]

I do something similar (but then in columns), but I us a macro to count.
So at the end of entering your data you could run the macro (assign it to a button) and it could give you the results in the last column or so.

Would that be OK?

 

[gjb5]

Thanks sijpie. That sounds like what I'm after. The data is in columns too. Woudl you be able to share the code. No poblem is you can't though.

Thanks,
gjb5

 

[kpark91]

Try

Sub countRep()
    Dim rep As Double, LR As Double, col As Double, i As Double, j As Double, repCurr As Double
    LR = Range("A" & Rows.Count).End(xlUp).Row
    col = 26
    rep = 0
    repCurr = 0
 
    For i = 2 To LR
        rep = 0
        repCurr = 0
        For j = 2 To col
             If Cells(i, j).Value = 1 Then 
                  repCurr = 0
             ElseIf Cells(i, j).Value = 2 Then
                  repCurr = repCurr + 1
             End If
             If repCurr > rep Then
                  rep = repCurr
             End If
        Next j
        Range("AA" & i).Value = rep
   Next i
 
End Sub

The code counts the repeating '2's.
Does not recognize any other values than '1's or '2's from colA to Z.

If you want any other features then just say so :P

 

[gjb5]

That's brilliant thank you!

in column AB, is there any way of identifying whether people have attended again after they last missed a session?

it'd be great if I could differentiate between:

1 2 1 1 2 2 2 1 1 1 1 1 1 1 2 2 2 2 2 1

and

1 2 1 1 2 2 2 1 1 1 1 2 2 2 2 2 2 2 2 2

Thanks,
gjb5

 

[kpark91]

Sure, here's the changed code:

Sub countRep()
    Dim rep As Double, LR As Double, col As Double, i As Double, j As Double, repCurr As Double
    LR = Range("A" & Rows.Count).End(xlUp).Row
    col = 26
    rep = 0
    repCurr = 0
 
    For i = 2 To LR
        rep = 0
        repCurr = 0
        For j = 2 To col
             If Cells(i, j).Value = 1 Then 
                  repCurr = 0
             ElseIf Cells(i, j).Value = 2 Then
                  repCurr = repCurr + 1
             End If
             If repCurr > rep Then
                  rep = repCurr
             End If
        Next j
        Range("AA" & i).Value = rep
        If Range("Z" & i).Value = 1 Then 
            Range("AB" & i).Value = "Back"
        ElseIf Range("Z" & i).Value = 2 Then
            Range("AB" & i).Value = "Not Back"
        End If
   Next i
 
End Sub