Lookup
Within the same minute.
"T. Valko" wrote:
You'll have to explain in greater detail what you mean by "closest".
6:00:00 - there is no data, returns a blank
6:15:00 - finds 6:15:20, returns 14
6:30:00 - finds 6:30:00, returns 15
6:45:00 - finds 6:45:04, returns 13
Why do you say for 6:00:00 there is no data? There is an entry for 6:12:56
which would be the "closest" to 6:00:00.
--
Biff
Microsoft Excel MVP
"Whitney" wrote in message
...
Thanks for your quick reply. Actually, I'm not looking to sum the numbers.
I
just need the number that is closest to the interval.
For example
6:00:00 - there is no data, returns a blank
6:15:00 - finds 6:15:20, returns 14
6:30:00 - finds 6:30:00, returns 15
6:45:00 - finds 6:45:04, returns 13
"OssieMac" wrote:
Hi Whitney,
Following formulas should work on your sample data. You need to make one
criteria greater than () and the next one less than or equal to (<=)
otherwise a time like 6:15:00 would get included in the 6:00:00 to
6:15:00
and also 6:15:00 to 6:30:00. Based on this, you might have to adjust the
formulas to return the required values. You might want to make the first
criteria equal to or greater (=) and the second one less than (<)
Examples assuming:
Dates are in column A
Times in column B
Agents in column C
6:00 to 6:15 =SUMIFS(C2:C39,B2:B39,"6:00:00",B2:B39,"<=6:15:00 ")
6:15 to 6:30 =SUMIFS(C2:C39,B2:B39,"6:15:00",B2:B39,"<=6:30:00 ")
6:30 to 6:45 =SUMIFS(C2:C39,B2:B39,"6:30:00",B2:B39,"<=6:45:00 ")
6:45 to 7:00 =SUMIFS(C2:C39,B2:B39,"6:45:00",B2:B39,"<=7:00:00 ")
7:00 to 7:15 =SUMIFS(C2:C39,B2:B39,"7:00:00",B2:B39,"<=7:15:00 ")
Feel free to get back to me if this does not answer your question as you
would like.
Regards,
Ossiemac
"Whitney" wrote:
Can anyone help with a lookup formula that will look at the cell closes
to
6:00, 6:15, 6:30, 6:45, 7:00, etc. and tell me what the number of
agents
we're (cell to the right) in each interval? There is no pattern, so the
lookup value will not always be in the same cell location each day.
Date Time Agents Available
11/2/2007 06:12:56 15
11/2/2007 06:14:56 15
11/2/2007 06:15:20 14
11/2/2007 06:15:28 15
11/2/2007 06:15:52 14
11/2/2007 06:16:40 13
11/2/2007 06:22:16 14
11/2/2007 06:23:28 15
11/2/2007 06:23:52 16
11/2/2007 06:24:16 15
11/2/2007 06:24:56 14
11/2/2007 06:25:12 13
11/2/2007 06:25:20 13
11/2/2007 06:25:28 12
11/2/2007 06:28:40 15
11/2/2007 06:29:20 14
11/2/2007 06:30:00 15
11/2/2007 06:30:08 14
11/2/2007 06:30:56 15
11/2/2007 06:31:04 14
11/2/2007 06:32:48 13
11/2/2007 06:44:48 13
11/2/2007 06:44:56 14
11/2/2007 06:45:04 13
11/2/2007 06:45:12 13
11/2/2007 06:45:20 13
11/2/2007 06:45:28 13
11/2/2007 06:45:36 12
11/2/2007 06:45:44 12
11/2/2007 06:45:52 12
11/2/2007 06:59:20 20
11/2/2007 06:59:28 20
11/2/2007 07:00:00 21
11/2/2007 07:00:08 22
11/2/2007 07:00:16 23
11/2/2007 07:00:40 22
11/2/2007 07:01:12 23
11/2/2007 07:02:24 22
|