How do I work out a retention percentage since first visit?

[mikeymo007]

Hopefully someone can help..

I'm keeping youth work statistics and need to track the participation rates of individuals since their first visit.

My spread-sheet has one row per user, with attendances marked as "P" in cells from column M onwards.
Effectively I need to be able to work out when the first attendance was and count all subsequent attendances, then dividing them by the number of cells from the first "P" up to the latest used column (this column might not be used in that particular row - if that person has not attended).

This is a live sheet. so needs to automatically update the ranges as extra attendance columns are added.


The idea is that we will be able to see, at any time, what percentage of weeks a member has attended since their first visit, so that we can target those who look likely to drop out - and potentially need help.

Thanks,

Mike

[Butcher1]

Please Login or Register  to view this content.

Assuming P means they attended, this will calculate what their attendance rate was. If P means absent, it will calculate their absence rate. Obviously a range of M1:O1 is too small to be useful, you will have to stretch that out to what you find appropriate.

[mikeymo007]

Please Login or Register  to view this content.

Assuming P means they attended, this will calculate what their attendance rate was. If P means absent, it will calculate their absence rate. Obviously a range of M1:O1 is too small to be useful, you will have to stretch that out to what you find appropriate.

Thanks Butcher but this doesn't give me what I need. I need a formula that will automatically create the range for each person, starting with the cell of the first "P" in their row and ending with the cell of the last week listed (not necessarily that of the last "P").
To have a fixed start point doesn't give a true representation as those who didn't first attend on the first date (most people) will have offset figures.

[Butcher1]

Can you upload a sample workbook? If it has sensitive data change/erase it prior to uploading.

If someone doesn't attend the first date, what goes into the appropriate cell?

[mikeymo007]

I've stripped just about all the data out and reduced it to a tiny number of users.
I'd like column K to contain the participation percentages with a usage total in column 3.
I guess I could just make L's formulae into a massive range but it seems inelegant. The real issue is K.

mrmsample.xlsm

[Butcher1]

I hid all rows but one for boy 2 as that one had attendance and absences.
=COUNTIF(M6:BN6,"p")/COUNTA(M6:BN6)
I didn't change the formula from my original suggestion, the Counta function will only count cells that are not blank and since you seem to be only marking attendance and absences nothing other than those would be counted, days prior to enrollment wouldn't be considered in the calculation. If there is something I am still not understanding please let me know.

If you want the formula to never need updating, I assume you will constantly expand the dates, make your range something like M6:AAA6, or at the maximum M6:XFD6.

[mikeymo007]

Ah, yeah.. let me explain the x; it's not an absence, it's a 'black mark' and in my workbook it counts as a presence.
So the correct percentage should be 63%.. 10 attendances / 16 weeks from the first attendance.


Just so I don't seem lazy, this is actually used for hundreds of people in each sheet, so working them out or altering them all would be a little tricky.
I really appreciate the help.

[mikeymo007]

Ah, yeah.. let me explain the x; it's not an absence, it's a 'black mark' and in my workbook it counts as a presence.
So the correct percentage should be 63%.. 10 attendances / 16 weeks from the first attendance.


Just so I don't seem lazy, this is actually used for hundreds of people in each sheet, so working them out or altering them all would be a little tricky.
I really appreciate the help.