|
repost: Min/Max help needed with a SumProduct
Hello Excel Yodas
For me this is an impossibility, but I know for some of you the force will guide you to the answer. The first table shows my data. Please keep in mind that immediately following Associate 1 there will be associate 2, Associate 3, etc€¦ Here is how I would like to return the data; A B C D Associate first logon time Lunch Duration Last Logoff time The associate name will already be listed in the A: column, so the times will likely need to match the A: entry. The total lunch time can be calculated by taking the entry marked €ś2LUN€ť and subtracting that time from the first €śAvailable€ť time following the €ś2LUN€ť Bottom line is I do not know how to write min/max to match a criteria with a sumproduct. I know I ask a lot, but I really could use the help and appreciate your effort. A B C D Name Event Type Date Start Time Associate 1 LOGON 2/8/2006 6:58:54 Associate 1 LOGOFF 2/8/2006 6:59:03 Associate 1 LOGON 2/8/2006 6:59:06 Associate 1 LOGON 2/8/2006 6:59:07 Associate 1 LOGON 2/8/2006 6:59:14 Associate 1 8WRP 2/8/2006 8:41:20 Associate 1 AVAILABLE 2/8/2006 8:43:46 Associate 1 1BRK 2/8/2006 9:27:51 Associate 1 AVAILABLE 2/8/2006 9:40:25 Associate 1 8WRP 2/8/2006 10:05:09 Associate 1 AVAILABLE 2/8/2006 10:07:28 Associate 1 8WRP 2/8/2006 10:20:32 Associate 1 AVAILABLE 2/8/200 10:27:58 Associate 1 8WRP 2/8/2006 11:12:14 Associate 1 AVAILABLE 2/8/2006 11:27:46 Associate 1 2LUN 2/8/2006 11:27:48 Associate 1 AVAILABLE 2/8/2006 12:05:32 Associate 1 8WRP 2/8/2006 12:35:06 Associate 1 AVAILABLE 2/8/2006 13:06:47 Associate 1 4CST 2/8/2006 13:06:49 Associate 1 AVAILABLE 2/8/2006 13:09:33 Associate 1 9OTH 2/8/2006 13:29:06 Associate 1 AVAILABLE 2/8/2006 13:50:04 Associate 1 1BRK 2/8/2006 13:50:28 Associate 1 AVAILABLE 2/8/2006 14:06:38 Associate 1 LOGOFF 2/8/2006 15:28:44 Associate 1 UNAVAILABLE 2/8/2006 15:28:50 Associate 1 AVAILABLE 2/8/2006 15:31:50 Associate 1 LOGOFF 2/8/2006 15:31:54 |
repost: Min/Max help needed with a SumProduct
I need an extra column, (more like young Luke, but I don't see Yoda or Obi
Wan in a hurry to respond) Note all formulas below assume that row 2 is the first row of data and row 5000 the last. Adjust instances of $2 and/or $5000 to your range. In call E2 add =IF(B2="2LUN",IF(ISERROR(MATCH("AVAILABLE",INDIREC T("B"&ROW()+1&":B500"),0)+ROW()),"",INDEX(D:D,MATC H("AVAILABLE",INDIRECT("B"&ROW()+1&":B500"),0)+ROW ()))-D2,"") Copy down In G1 type "Associate 1" In H1 =MIN(IF(A$2:A$5000=G1,E$2:E$5000)) In I1 =MAX(IF(A$2:A$5000=G1,E$2:E$5000)) Both the MIN and MAX are array formulas, you must commit with control+shift+enter Now you can type in other Associates below in column G and copy H1:I1 down Finding the next "AVAILABLE" after each "2LUN" doesn't seem to want to fit inside the array Min or Max. I tested this minimally. If you have a problem, please indicate the condition that caused failure. One problem you might have is if the associate goes to lunch, gets tanked, and comes back tomorrow. If you want to only count same day returns, use this in E2 instead of the previous: =IF(B2="2LUN",IF(ISERROR(MATCH("AVAILABLE",INDIREC T("B"&ROW()+1&":B5000"),0)+ROW()),"",IF(INDEX(C:C, MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B5000"),0 )+ROW())=C2,INDEX(D:D,MATCH("AVAILABLE",INDIRECT(" B"&ROW()+1&":B5000"),0)+ROW())-D2,"")),"") Good luck "JR" wrote in message ... Hello Excel Yoda's For me this is an impossibility, but I know for some of you the force will guide you to the answer. The first table shows my data. Please keep in mind that immediately following Associate 1 there will be associate 2, Associate 3, etc. Here is how I would like to return the data; A B C D Associate first logon time Lunch Duration Last Logoff time The associate name will already be listed in the A: column, so the times will likely need to match the A: entry. The total lunch time can be calculated by taking the entry marked "2LUN" and subtracting that time from the first "Available" time following the "2LUN" Bottom line is I do not know how to write min/max to match a criteria with a sumproduct. I know I ask a lot, but I really could use the help and appreciate your effort. A B C D Name Event Type Date Start Time Associate 1 LOGON 2/8/2006 6:58:54 Associate 1 LOGOFF 2/8/2006 6:59:03 Associate 1 LOGON 2/8/2006 6:59:06 Associate 1 LOGON 2/8/2006 6:59:07 Associate 1 LOGON 2/8/2006 6:59:14 Associate 1 8WRP 2/8/2006 8:41:20 Associate 1 AVAILABLE 2/8/2006 8:43:46 Associate 1 1BRK 2/8/2006 9:27:51 Associate 1 AVAILABLE 2/8/2006 9:40:25 Associate 1 8WRP 2/8/2006 10:05:09 Associate 1 AVAILABLE 2/8/2006 10:07:28 Associate 1 8WRP 2/8/2006 10:20:32 Associate 1 AVAILABLE 2/8/200 10:27:58 Associate 1 8WRP 2/8/2006 11:12:14 Associate 1 AVAILABLE 2/8/2006 11:27:46 Associate 1 2LUN 2/8/2006 11:27:48 Associate 1 AVAILABLE 2/8/2006 12:05:32 Associate 1 8WRP 2/8/2006 12:35:06 Associate 1 AVAILABLE 2/8/2006 13:06:47 Associate 1 4CST 2/8/2006 13:06:49 Associate 1 AVAILABLE 2/8/2006 13:09:33 Associate 1 9OTH 2/8/2006 13:29:06 Associate 1 AVAILABLE 2/8/2006 13:50:04 Associate 1 1BRK 2/8/2006 13:50:28 Associate 1 AVAILABLE 2/8/2006 14:06:38 Associate 1 LOGOFF 2/8/2006 15:28:44 Associate 1 UNAVAILABLE 2/8/2006 15:28:50 Associate 1 AVAILABLE 2/8/2006 15:31:50 Associate 1 LOGOFF 2/8/2006 15:31:54 |
repost: Min/Max help needed with a SumProduct
I do nto see how this is matching to the associate. I tried it just like you
posted with no success - I also made a couple changes and still no success. Any other suggestions? I appreciate your help. "Bob Tarburton" wrote: I need an extra column, (more like young Luke, but I don't see Yoda or Obi Wan in a hurry to respond) Note all formulas below assume that row 2 is the first row of data and row 5000 the last. Adjust instances of $2 and/or $5000 to your range. In call E2 add =IF(B2="2LUN",IF(ISERROR(MATCH("AVAILABLE",INDIREC T("B"&ROW()+1&":B500"),0)+ROW()),"",INDEX(D:D,MATC H("AVAILABLE",INDIRECT("B"&ROW()+1&":B500"),0)+ROW ()))-D2,"") Copy down In G1 type "Associate 1" In H1 =MIN(IF(A$2:A$5000=G1,E$2:E$5000)) In I1 =MAX(IF(A$2:A$5000=G1,E$2:E$5000)) Both the MIN and MAX are array formulas, you must commit with control+shift+enter Now you can type in other Associates below in column G and copy H1:I1 down Finding the next "AVAILABLE" after each "2LUN" doesn't seem to want to fit inside the array Min or Max. I tested this minimally. If you have a problem, please indicate the condition that caused failure. One problem you might have is if the associate goes to lunch, gets tanked, and comes back tomorrow. If you want to only count same day returns, use this in E2 instead of the previous: =IF(B2="2LUN",IF(ISERROR(MATCH("AVAILABLE",INDIREC T("B"&ROW()+1&":B5000"),0)+ROW()),"",IF(INDEX(C:C, MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B5000"),0 )+ROW())=C2,INDEX(D:D,MATCH("AVAILABLE",INDIRECT(" B"&ROW()+1&":B5000"),0)+ROW())-D2,"")),"") Good luck "JR" wrote in message ... Hello Excel Yoda's For me this is an impossibility, but I know for some of you the force will guide you to the answer. The first table shows my data. Please keep in mind that immediately following Associate 1 there will be associate 2, Associate 3, etc. Here is how I would like to return the data; A B C D Associate first logon time Lunch Duration Last Logoff time The associate name will already be listed in the A: column, so the times will likely need to match the A: entry. The total lunch time can be calculated by taking the entry marked "2LUN" and subtracting that time from the first "Available" time following the "2LUN" Bottom line is I do not know how to write min/max to match a criteria with a sumproduct. I know I ask a lot, but I really could use the help and appreciate your effort. A B C D Name Event Type Date Start Time Associate 1 LOGON 2/8/2006 6:58:54 Associate 1 LOGOFF 2/8/2006 6:59:03 Associate 1 LOGON 2/8/2006 6:59:06 Associate 1 LOGON 2/8/2006 6:59:07 Associate 1 LOGON 2/8/2006 6:59:14 Associate 1 8WRP 2/8/2006 8:41:20 Associate 1 AVAILABLE 2/8/2006 8:43:46 Associate 1 1BRK 2/8/2006 9:27:51 Associate 1 AVAILABLE 2/8/2006 9:40:25 Associate 1 8WRP 2/8/2006 10:05:09 Associate 1 AVAILABLE 2/8/2006 10:07:28 Associate 1 8WRP 2/8/2006 10:20:32 Associate 1 AVAILABLE 2/8/200 10:27:58 Associate 1 8WRP 2/8/2006 11:12:14 Associate 1 AVAILABLE 2/8/2006 11:27:46 Associate 1 2LUN 2/8/2006 11:27:48 Associate 1 AVAILABLE 2/8/2006 12:05:32 Associate 1 8WRP 2/8/2006 12:35:06 Associate 1 AVAILABLE 2/8/2006 13:06:47 Associate 1 4CST 2/8/2006 13:06:49 Associate 1 AVAILABLE 2/8/2006 13:09:33 Associate 1 9OTH 2/8/2006 13:29:06 Associate 1 AVAILABLE 2/8/2006 13:50:04 Associate 1 1BRK 2/8/2006 13:50:28 Associate 1 AVAILABLE 2/8/2006 14:06:38 Associate 1 LOGOFF 2/8/2006 15:28:44 Associate 1 UNAVAILABLE 2/8/2006 15:28:50 Associate 1 AVAILABLE 2/8/2006 15:31:50 Associate 1 LOGOFF 2/8/2006 15:31:54 |
repost: Min/Max help needed with a SumProduct
Right, I didn't condider which Associate returned Available, just took the
first one. In cell E2 add =IF(B2="2LUN",IF(ISERROR(MATCH(1,INDEX((A$2:A$5000 =A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)ROW (B2)),0),0)),"",INDEX(D$2:D$5000,MATCH(1,INDEX((A$ 2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5 000)ROW(B2)),0),0))-D2),"") copy down Or if you want to require same day return =IF(B2="2LUN",IF(ISERROR(MATCH(1,INDEX((A$2:A$5000 =A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)ROW (B2)),0),0)),"",IF(INDEX(C$2:C$5000,MATCH(1,INDEX( (A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2: B$5000)ROW(B2)),0),0))=C2,INDEX(D$2:D$5000,MATCH( 1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*( ROW(B$2:B$5000)ROW(B2)),0),0))-D2,"")),"") Same MIN and MAX as before Still leaving the beyond minimal testing to you Sorry about that 1st one "JR" wrote in message ... I do nto see how this is matching to the associate. I tried it just like you posted with no success - I also made a couple changes and still no success. Any other suggestions? I appreciate your help. "Bob Tarburton" wrote: I need an extra column, (more like young Luke, but I don't see Yoda or Obi Wan in a hurry to respond) Note all formulas below assume that row 2 is the first row of data and row 5000 the last. Adjust instances of $2 and/or $5000 to your range. In call E2 add XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx Copy down In G1 type "Associate 1" In H1 =MIN(IF(A$2:A$5000=G1,E$2:E$5000)) In I1 =MAX(IF(A$2:A$5000=G1,E$2:E$5000)) Both the MIN and MAX are array formulas, you must commit with control+shift+enter Now you can type in other Associates below in column G and copy H1:I1 down Finding the next "AVAILABLE" after each "2LUN" doesn't seem to want to fit inside the array Min or Max. I tested this minimally. If you have a problem, please indicate the condition that caused failure. One problem you might have is if the associate goes to lunch, gets tanked, and comes back tomorrow. If you want to only count same day returns, use this in E2 instead of the previous: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Good luck "JR" wrote in message ... Hello Excel Yoda's For me this is an impossibility, but I know for some of you the force will guide you to the answer. The first table shows my data. Please keep in mind that immediately following Associate 1 there will be associate 2, Associate 3, etc. Here is how I would like to return the data; A B C D Associate first logon time Lunch Duration Last Logoff time The associate name will already be listed in the A: column, so the times will likely need to match the A: entry. The total lunch time can be calculated by taking the entry marked "2LUN" and subtracting that time from the first "Available" time following the "2LUN" Bottom line is I do not know how to write min/max to match a criteria with a sumproduct. I know I ask a lot, but I really could use the help and appreciate your effort. A B C D Name Event Type Date Start Time Associate 1 LOGON 2/8/2006 6:58:54 Associate 1 LOGOFF 2/8/2006 6:59:03 Associate 1 LOGON 2/8/2006 6:59:06 Associate 1 LOGON 2/8/2006 6:59:07 Associate 1 LOGON 2/8/2006 6:59:14 Associate 1 8WRP 2/8/2006 8:41:20 Associate 1 AVAILABLE 2/8/2006 8:43:46 Associate 1 1BRK 2/8/2006 9:27:51 Associate 1 AVAILABLE 2/8/2006 9:40:25 Associate 1 8WRP 2/8/2006 10:05:09 Associate 1 AVAILABLE 2/8/2006 10:07:28 Associate 1 8WRP 2/8/2006 10:20:32 Associate 1 AVAILABLE 2/8/200 10:27:58 Associate 1 8WRP 2/8/2006 11:12:14 Associate 1 AVAILABLE 2/8/2006 11:27:46 Associate 1 2LUN 2/8/2006 11:27:48 Associate 1 AVAILABLE 2/8/2006 12:05:32 Associate 1 8WRP 2/8/2006 12:35:06 Associate 1 AVAILABLE 2/8/2006 13:06:47 Associate 1 4CST 2/8/2006 13:06:49 Associate 1 AVAILABLE 2/8/2006 13:09:33 Associate 1 9OTH 2/8/2006 13:29:06 Associate 1 AVAILABLE 2/8/2006 13:50:04 Associate 1 1BRK 2/8/2006 13:50:28 Associate 1 AVAILABLE 2/8/2006 14:06:38 Associate 1 LOGOFF 2/8/2006 15:28:44 Associate 1 UNAVAILABLE 2/8/2006 15:28:50 Associate 1 AVAILABLE 2/8/2006 15:31:50 Associate 1 LOGOFF 2/8/2006 15:31:54 |
| All times are GMT +1. The time now is 10:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com