Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Deleting/IDing Rows that Don't Meet Criteria
I am doing research for my thesis. This involved putting a survey on the Internet. Some people responded to the survey in less than 30 seconds, and I want to delete these participants because they probably did not read the questions. Column A1 lists when they began the survey and column A2 lists when they finished the survey: A1 A2 August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM I would like to identify cases where the partipant took less than 30 seconds, so the output might have 1s if it's okay and 0s if it doesn't: A1 A2 A3 August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM 1 August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM 1 August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM 1 August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM 0 August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM 1 Is there any way to do this? I realize that it's complicated but I have 2000 participants and I don't want to do it by hand! Thank you! -- LittleAndLost ------------------------------------------------------------------------ LittleAndLost's Profile: http://www.excelforum.com/member.php...o&userid=16010 View this thread: http://www.excelforum.com/showthread...hreadid=274767 |
#2
|
|||
|
|||
Hi!
It's somewhat complicated but probably not for the reason you're thinking. Just looking at those "date/times" tells me that they're not real Excel dates/times but rather TEXT strings. If they were real dates/times then it would be a simple matter of just subtracting A1 from B1. What you could do is to use the TEXT TO COLUMNS menu command and separate out the actual times into another column. I did this and separated the string like this: column A column B column D column E August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM Now, you can use a formula like this: =IF(E1-B1+(B1E1)<TIME(0,0,30),0,1) Post back if you need help on using Text to Columns. Biff -----Original Message----- I am doing research for my thesis. This involved putting a survey on the Internet. Some people responded to the survey in less than 30 seconds, and I want to delete these participants because they probably did not read the questions. Column A1 lists when they began the survey and column A2 lists when they finished the survey: A1 A2 August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM I would like to identify cases where the partipant took less than 30 seconds, so the output might have 1s if it's okay and 0s if it doesn't: A1 A2 A3 August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM 1 August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM 1 August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM 1 August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM 0 August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM 1 Is there any way to do this? I realize that it's complicated but I have 2000 participants and I don't want to do it by hand! Thank you! -- LittleAndLost ---------------------------------------------------------- -------------- LittleAndLost's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=16010 View this thread: http://www.excelforum.com/showthread...hreadid=274767 . |
#3
|
|||
|
|||
Assuming that the times are Excel numbers rather than text strings you could use a formula along the lines of: IF(End Time Cell - Start Time Cell < TIME(0,0,30),1,0) Copy this down and cases where less than 30 seconds ar taken show a 1 in this column. You can autofilter based on this result to exclude the cases taking less than 30 seconds. -- gatwickxx ------------------------------------------------------------------------ gatwickxx's Profile: http://www.excelforum.com/member.php...o&userid=15613 View this thread: http://www.excelforum.com/showthread...hreadid=274767 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Adding Rows to Master Sheet | New Users to Excel | |||
Print cells that meet conditional formatting criteria | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Count rows based on multiple criteria | Excel Worksheet Functions |