ronald.gravendeel
New Member
- Joined
- Jun 13, 2005
- Messages
- 7
Hello,
I'm looking for a way to get a VLOOKUP (or equivalent effect) on a value that falls within a range, in stead of a predefined value.
Say for instance, I'm timing the test of 3 students, Jim, Bob & Ann. For each of the students, I note when they begin a new question. the resulting data sheet I get is:
<table BORDER=1><TR><TD>Jim</TD><TD>Bob</TD><TD>Ann</TD></TR>
<TR><TD> 9:05 1 </TD><TD>9:03 1 </TD><TD>9:02 1</TD></TR>
<TR><TD>9:15 2</TD><TD>9:16 2</TD><TD>9:09 2</TD></TR>
<TR><TD> 9:30 3</TD><TD>9:31 3</TD><TD>9:17 3</TD></TR>
<TR><TD> 9:46 4</TD><TD>9:49 4</TD><TD>9:29 4</TD></TABLE>
I want to make a table that indicates for each agent which question they began with a 5 minute period. So that table would look like this:
<table BORDER=1><TR><TD></TD><TD> Jim </TD><TD>Bob</TD><TD>Ann</TD></TR>
<TR><TD>9:00 - 9:05</TD><TD>1</TD><TD>1</TD><TD> 1</TD></TR>
<TR><TD>9:05 - 9:10</TD><TD></TD><TD></TD><TD>2</TD></TR>
<TR><TD>9:10 - 9:15</TD><TD>2</TD><TD></TD></TR>
<TR><TD>9:15 - 9:20</TD><TD></TD><TD>2</TD><TD>3</TD></TR>
<TR><TD>9:20 - 9:25</TD><TD></TD><TD></TD></TR>
<TR><TD>9:25 - 9:30</TD><TD>3</TD><TD></TD><TD> 4</TD></TR>
<TR><TD>9:30 - 9:35</TD><TD></TD><TD>3</TD><TD></TD></TR>
<TR><TD>9:35 - 9:40</TD><TD></TD><TD></TD><TD></TD></TR>
<TR><TD>9:40 - 9:45</TD><TD></TD><TD></TD><TD></TD></TR>
<TR><TD>9:45 - 9:50</TD><TD>4</TD><TD>4</TD><TD></TD></TABLE>
How can I get VLOOKUP (or another function) to recognize that a given time lies within the predefined time-range?
Thanks in advance</p>
I'm looking for a way to get a VLOOKUP (or equivalent effect) on a value that falls within a range, in stead of a predefined value.
Say for instance, I'm timing the test of 3 students, Jim, Bob & Ann. For each of the students, I note when they begin a new question. the resulting data sheet I get is:
<table BORDER=1><TR><TD>Jim</TD><TD>Bob</TD><TD>Ann</TD></TR>
<TR><TD> 9:05 1 </TD><TD>9:03 1 </TD><TD>9:02 1</TD></TR>
<TR><TD>9:15 2</TD><TD>9:16 2</TD><TD>9:09 2</TD></TR>
<TR><TD> 9:30 3</TD><TD>9:31 3</TD><TD>9:17 3</TD></TR>
<TR><TD> 9:46 4</TD><TD>9:49 4</TD><TD>9:29 4</TD></TABLE>
I want to make a table that indicates for each agent which question they began with a 5 minute period. So that table would look like this:
<table BORDER=1><TR><TD></TD><TD> Jim </TD><TD>Bob</TD><TD>Ann</TD></TR>
<TR><TD>9:00 - 9:05</TD><TD>1</TD><TD>1</TD><TD> 1</TD></TR>
<TR><TD>9:05 - 9:10</TD><TD></TD><TD></TD><TD>2</TD></TR>
<TR><TD>9:10 - 9:15</TD><TD>2</TD><TD></TD></TR>
<TR><TD>9:15 - 9:20</TD><TD></TD><TD>2</TD><TD>3</TD></TR>
<TR><TD>9:20 - 9:25</TD><TD></TD><TD></TD></TR>
<TR><TD>9:25 - 9:30</TD><TD>3</TD><TD></TD><TD> 4</TD></TR>
<TR><TD>9:30 - 9:35</TD><TD></TD><TD>3</TD><TD></TD></TR>
<TR><TD>9:35 - 9:40</TD><TD></TD><TD></TD><TD></TD></TR>
<TR><TD>9:40 - 9:45</TD><TD></TD><TD></TD><TD></TD></TR>
<TR><TD>9:45 - 9:50</TD><TD>4</TD><TD>4</TD><TD></TD></TABLE>
How can I get VLOOKUP (or another function) to recognize that a given time lies within the predefined time-range?
Thanks in advance</p>