Hi,
I have the following formula which works well for a subset of subjects joining the study at various times throughout the year. It calculates the # of unique days when they logged something in their seizure diary. Sometimes this can mean no entry on a given day whereas sometimes this can be multiple entries if they had a particularly bad day. I'm only looking for the # of days with any entries whatsoever, regardless if this were 1 entry or 100.
{=SUM(SIGN(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000='Post-processed data'!C4,'Seizure diary'!$F$13:$F$50000),'Seizure diary'!$F$13:$F$50000)))}
where
'Post-processed data'!C4 = unique subject identifier
'Seizure diary'!$F$13:$F$50000 = column with dates (only); columns G onwards would have subsequent info regarding seizures, which is of no interest here.
However, can I take it to the next level so that the formula only takes into account the entries in a particular time range, with a criterion?
More specifically, I'm interested in a range between 15 and 5 days from today (the day the report is run). I guess I need to set up helper columns first, to define this range for each subject...
Finally, given that some of the subjects might not even have that data (if they joined recently, i.e. less than 15 days ago), can I add a condition which would trigger the calc in case the subject stays in the study for more than 15 days (otherwise returning N/A)? For the formula above, the date the subject joined the study is in cell 'Post-processed data'!C4
Wondering if I made myself clear...
I have the following formula which works well for a subset of subjects joining the study at various times throughout the year. It calculates the # of unique days when they logged something in their seizure diary. Sometimes this can mean no entry on a given day whereas sometimes this can be multiple entries if they had a particularly bad day. I'm only looking for the # of days with any entries whatsoever, regardless if this were 1 entry or 100.
{=SUM(SIGN(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000='Post-processed data'!C4,'Seizure diary'!$F$13:$F$50000),'Seizure diary'!$F$13:$F$50000)))}
where
'Post-processed data'!C4 = unique subject identifier
'Seizure diary'!$F$13:$F$50000 = column with dates (only); columns G onwards would have subsequent info regarding seizures, which is of no interest here.
However, can I take it to the next level so that the formula only takes into account the entries in a particular time range, with a criterion?
More specifically, I'm interested in a range between 15 and 5 days from today (the day the report is run). I guess I need to set up helper columns first, to define this range for each subject...
Finally, given that some of the subjects might not even have that data (if they joined recently, i.e. less than 15 days ago), can I add a condition which would trigger the calc in case the subject stays in the study for more than 15 days (otherwise returning N/A)? For the formula above, the date the subject joined the study is in cell 'Post-processed data'!C4
Wondering if I made myself clear...