|
Lookup
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 |
Lookup
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 |
Lookup
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 |
Lookup
On Nov 5, 6:17 pm, Whitney wrote:
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- Hide quoted text - - Show quoted text - try a vlookup. If columns are Date=A, Time=B, Agents=C If cell B1=desired time =vlookup(B1,B2:C###,2,true) vlookup(lookup_value,table_array,column,True/False) the true means that it will find the closest approximate match. The table must be sorted ascending by time to work properly. |
Lookup
Hi Whitney,
I was right off the mark with my interpretation of what you want. I can't think of any way of achieving this with a worksheet function. I could do it with a macro if you are interested in that. However, do the times always increment with each row. What I mean by this is there likely to be a time in any row which is less that a time on a previous row? Regards, OssieMac "Whitney" wrote: 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 |
Lookup
I think with Vlookup if an exact match is not found, the next largest value
that IS LESS than lookup value is returned; Not necessarily the closest value which might be greater than the lookup value. Regards, OssieMac " wrote: On Nov 5, 6:17 pm, Whitney wrote: 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- Hide quoted text - - Show quoted text - try a vlookup. If columns are Date=A, Time=B, Agents=C If cell B1=desired time =vlookup(B1,B2:C###,2,true) vlookup(lookup_value,table_array,column,True/False) the true means that it will find the closest approximate match. The table must be sorted ascending by time to work properly. |
Lookup
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 |
Lookup
No the times are always in chronological order. However the same times will
not always be in the same cells, which is why I didn't think I could use a macro. For example 6:15:20 will not always be in cell B3, nor will there always be a 6:15:20 on each report. "OssieMac" wrote: Hi Whitney, I was right off the mark with my interpretation of what you want. I can't think of any way of achieving this with a worksheet function. I could do it with a macro if you are interested in that. However, do the times always increment with each row. What I mean by this is there likely to be a time in any row which is less that a time on a previous row? Regards, OssieMac "Whitney" wrote: 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 |
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 |
Lookup
the times are always in chronological order.
Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
Lookup
I copied the formula and hit ctrl+shift+enter and got the bracketts, but it's
coming back blank. "T. Valko" wrote: the times are always in chronological order. Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
Lookup
Here's a sample file using your sample data with the results you said you
should get. Whitney.xls 15kb http://cjoint.com/?lheDjBPAZc -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the formula and hit ctrl+shift+enter and got the bracketts, but it's coming back blank. "T. Valko" wrote: the times are always in chronological order. Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
Lookup
I copied the exact formula from the spreadsheet, hit ctrl+shift+enter and got
the bracketts, but it's still coming back blank for all time frames. I even checked the fromating for columns B & C, B is h:mm:ss, c is number. Is there something else that I'm missing? "T. Valko" wrote: Here's a sample file using your sample data with the results you said you should get. Whitney.xls 15kb http://cjoint.com/?lheDjBPAZc -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the formula and hit ctrl+shift+enter and got the bracketts, but it's coming back blank. "T. Valko" wrote: the times are always in chronological order. Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
Lookup
Can you send a copy of the file to me so I can see what's going on? If so,
I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the exact formula from the spreadsheet, hit ctrl+shift+enter and got the bracketts, but it's still coming back blank for all time frames. I even checked the fromating for columns B & C, B is h:mm:ss, c is number. Is there something else that I'm missing? "T. Valko" wrote: Here's a sample file using your sample data with the results you said you should get. Whitney.xls 15kb http://cjoint.com/?lheDjBPAZc -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the formula and hit ctrl+shift+enter and got the bracketts, but it's coming back blank. "T. Valko" wrote: the times are always in chronological order. Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
Lookup
I sent it to xlhelp
"T. Valko" wrote: Can you send a copy of the file to me so I can see what's going on? If so, I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the exact formula from the spreadsheet, hit ctrl+shift+enter and got the bracketts, but it's still coming back blank for all time frames. I even checked the fromating for columns B & C, B is h:mm:ss, c is number. Is there something else that I'm missing? "T. Valko" wrote: Here's a sample file using your sample data with the results you said you should get. Whitney.xls 15kb http://cjoint.com/?lheDjBPAZc -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the formula and hit ctrl+shift+enter and got the bracketts, but it's coming back blank. "T. Valko" wrote: the times are always in chronological order. Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
Lookup
OK, I got it. You'll be getting my email reply any minute.
It was a formatting problem. The times were really TEXT strings that looked like times. -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I sent it to xlhelp "T. Valko" wrote: Can you send a copy of the file to me so I can see what's going on? If so, I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the exact formula from the spreadsheet, hit ctrl+shift+enter and got the bracketts, but it's still coming back blank for all time frames. I even checked the fromating for columns B & C, B is h:mm:ss, c is number. Is there something else that I'm missing? "T. Valko" wrote: Here's a sample file using your sample data with the results you said you should get. Whitney.xls 15kb http://cjoint.com/?lheDjBPAZc -- Biff Microsoft Excel MVP "Whitney" wrote in message ... I copied the formula and hit ctrl+shift+enter and got the bracketts, but it's coming back blank. "T. Valko" wrote: the times are always in chronological order. Within the same minute. Try this array formula**: F2 = 6:00:00 F3 = 6:15:00 F4 = 6:30:00 etc =IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0))) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Whitney" wrote in message ... 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 |
| All times are GMT +1. The time now is 03:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com