NEED A FREQUENCY FORMULA:
How many of the COHORT 2015 students in the 1st COUNTIFS statement (488) returned in the 2nd COUNTIFS OR FREQUENCY statement (2149) of the 2343 duplicates (all formulas found below).
I've been researching FREQUENCY (found these below) which will count the duplicate STUID's, BUT how do I incorporate the required criteria found in the (COUNTIFS #1) which is contingent on the result from COUNTIFS #2 into a single FREQUENCY formula to get 65%
Basically, can a COUNTIFS or FREQUENCY be placed inside another FREQUENCY result?
COUNTIFS #2 counts the number of students in TERM = "16FALL" (result =2149); of those 2149 students, how many in COUNTIFS #1 (the Cohort) appear / returned in COUNTIFS #2? The result =315 which will then be divided by COUNTIFS #1 (the Cohort) that gives the 65% that I am seeking. Something like IF((COUNTIFS #2 result),IF(AND(COUNTIFS #1 result)... then give me duplicate count / COUNTIFS
NOTE: The MASTER File contains Fall 2007 to Fall 2017 (07FALL to 17FALL) data consisting of 42000 rows and 140 columns. The STUDENT ID (STUID) and TERM are the main components.
Definition of Retention Rate (COHORT): The number of Full-Time, First-Time Freshman (FTF) Undergraduates who are Degree Seeking and have returned the following Fall Term.
Below are the columns used to calculate Retention Rates.
COLUMN A = STUID (COUNT the duplicates) COHORT that returned the following term
COLUMN B = TERM (15FALL and 16FALL)
COLUMN C = FT/PT (FT)
COLUMN G = LEVEL (UG)
COLUMN L = ENROLL STATUS (FF)
COLUMN O = PROGRAM ID (<> ND.*)
KNOWNS:
COUNTIFS #1 COHORT 2015 = 488
=COUNTIFS('DATA SAMPLE'!B:B,"15FALL",'DATA SAMPLE'!G:G,"UG",'DATA SAMPLE'!C:C,"FT",'DATA SAMPLE'!L:L,"FF",'DATA SAMPLE'!O:O,"<>ND.*")
COUNTIFS #2 FALL 2016 ENROLLMENT = 2149
=COUNTIFS('DATA SAMPLE'!B:B,"16FALL")
FREQUENCY COUNT FOR FALL 2016 ENROLLMENT = 2149
=SUM(--(FREQUENCY(IF('DATA SAMPLE'!B2:B4474="16FALL",MATCH('DATA SAMPLE'!A2:A4474,A2:A4474,0)),ROW('DATA SAMPLE'!B2:B4474)-ROW(B2)+1)>0))
DUPLICATES = 2343
{=SUM(IF(FREQUENCY(IF(LEN('DATA SAMPLE'!A2:A5000)>0,MATCH('DATA SAMPLE'!A2:A5000,A2:A5000,0),""),IF(LEN('DATA SAMPLE'!A2:A5000)>0,MATCH('DATA SAMPLE'!A2:A5000,A2:A5000,0),""))>0,1))}
** PIVOT TABLES and VLOOKUP USED to check work (TABLES found in COLUMNS V-AC) of attached file to get RETENTION # AND %
COHORT FALL 2015 RETURN FALL 2016 = 315 (cell Y6)
RETENTION RATE = 65% (cell Y7)
RETENTION RATE FORMULA SETUP
COHORT 2015 RETURN IN FALL 2016/COHORT 2015 = RETENTION RATE %
OUTPUT ANSWER SHOULD EQUAL BELOW:
= 315 (returned in Fall 2016) / 488 (Cohort 2015 - COUNTIFS #1) = 65%.
Bookmarks