I have a list of dates in a table that correspond to steps along a process--it is for respondents who get invited to take a survey. Invitations ('invite') are sent to respondents, who then 'accept' the invitation, are then administered ('administer') the survey, and finally the survey gets scored ('score').
I would like to know a) how long each step of the process takes on average, and b) how long individual respondents take for each step. I would like to be able to choose any two steps (e.g., 'invite', 'score') to run these analyses. I have put in a dropdown in I2 and J2 to choose which two columns to compare. I would like to essentially create a second table that compares the two columns side-by-side, so I can see how long each respondent has taken.
I have tried =INDEX(Table1,MATCH($I$2,Table1[#Headers],0),MATCH($I$2,Table1[#Headers],0)) (and $J$2 for the second column), but I have two issues: it is returning the incorrect date, and I am not sure how to drag this formula down to retrieve all data from the corresponding column. Can someone offer any advice using formulas (I'm in an environment that frowns on macros/vba)?
The original spreadsheet has 40 columns of dates, which I believe are static. There are currently 36 respondents, and this list is dynamic. I hope to put this together for a colleague, so I am aiming for something "user-friendly."
Thank you in advance!
I would like to know a) how long each step of the process takes on average, and b) how long individual respondents take for each step. I would like to be able to choose any two steps (e.g., 'invite', 'score') to run these analyses. I have put in a dropdown in I2 and J2 to choose which two columns to compare. I would like to essentially create a second table that compares the two columns side-by-side, so I can see how long each respondent has taken.
I have tried =INDEX(Table1,MATCH($I$2,Table1[#Headers],0),MATCH($I$2,Table1[#Headers],0)) (and $J$2 for the second column), but I have two issues: it is returning the incorrect date, and I am not sure how to drag this formula down to retrieve all data from the corresponding column. Can someone offer any advice using formulas (I'm in an environment that frowns on macros/vba)?
The original spreadsheet has 40 columns of dates, which I believe are static. There are currently 36 respondents, and this list is dynamic. I hope to put this together for a colleague, so I am aiming for something "user-friendly."
Thank you in advance!
| survey dates.xlsx | |||
|---|---|---|---|
| D | |||
| 12 | |||
Sheet1 | |||
