| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Times
Call ID Status Type Assigned Resolved Database ID Name
754776 Closed Problem 05/22/08 05/22/08 dschnabe MFGSYS 751502 Closed Problem 05/09/08 05/09/08 dschnabe SAP_MFG 747345 Closed Problem 04/21/08 04/21/08 dschnabe MFGSYS 746533 Closed Problem 04/16/08 04/18/08 dschnabe MFGSYS 746472 Closed Problem 04/16/08 04/17/08 dschnabe SAP_MFG 745730 Closed Problem 04/14/08 04/15/08 dschnabe MFGSYS 741597 Closed Problem 03/26/08 03/27/08 dschnabe SAP_MFG 738441 Closed Problem 03/12/08 03/18/08 dschnabe SAP_MFG 738424 Closed Problem 03/12/08 03/13/08 dschnabe SAP_MFG 737777 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737773 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737271 Closed Problem 03/07/08 03/10/08 dschnabe MFGSYS 737253 Closed Problem 03/07/08 03/07/08 dschnabe MFGSYS 755669 Closed Problem 05/27/08 05/28/08 dschnabe MFGSYS How can I determine if user DSCHNABE has more than 5 CALLID's in the same period of time? ex. if he has 7 call id's open on 03/27/08 then "Max Adherence Problem" |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Times
This is how I would do it: Make a column of dates. I used I2:I84 for 3/7/2008
through 5/28/2008 (adjust for the time period you actually care about. Next to this column, I used a formula that tells me how many items dschanbe is working on at that date: In J2 I used =SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15)) and copied down, where column D is Assigned, E is Resolved, and F is Database ID. Here you could replace "DSCHNABE" with another name or a reference to the name. Now that that's done, there are several options. You could nest it inside an IF statement, =IF(SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15))5,"Max Adherence Problem",""), or use a chart, or conditional formatting. It really depends on what you're trying to do and your preference. I would use a chart in this example. If your data contains most Database ID's than just DSCHABE, you could use one column for each, and these would be one line on a line graph. Any time the line crossed 5, you would have your problem. "sed" wrote: Call ID Status Type Assigned Resolved Database ID Name 754776 Closed Problem 05/22/08 05/22/08 dschnabe MFGSYS 751502 Closed Problem 05/09/08 05/09/08 dschnabe SAP_MFG 747345 Closed Problem 04/21/08 04/21/08 dschnabe MFGSYS 746533 Closed Problem 04/16/08 04/18/08 dschnabe MFGSYS 746472 Closed Problem 04/16/08 04/17/08 dschnabe SAP_MFG 745730 Closed Problem 04/14/08 04/15/08 dschnabe MFGSYS 741597 Closed Problem 03/26/08 03/27/08 dschnabe SAP_MFG 738441 Closed Problem 03/12/08 03/18/08 dschnabe SAP_MFG 738424 Closed Problem 03/12/08 03/13/08 dschnabe SAP_MFG 737777 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737773 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737271 Closed Problem 03/07/08 03/10/08 dschnabe MFGSYS 737253 Closed Problem 03/07/08 03/07/08 dschnabe MFGSYS 755669 Closed Problem 05/27/08 05/28/08 dschnabe MFGSYS How can I determine if user DSCHNABE has more than 5 CALLID's in the same period of time? ex. if he has 7 call id's open on 03/27/08 then "Max Adherence Problem" |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Times
Thank you so much!
-- sss "sb1920alk" wrote: This is how I would do it: Make a column of dates. I used I2:I84 for 3/7/2008 through 5/28/2008 (adjust for the time period you actually care about. Next to this column, I used a formula that tells me how many items dschanbe is working on at that date: In J2 I used =SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15)) and copied down, where column D is Assigned, E is Resolved, and F is Database ID. Here you could replace "DSCHNABE" with another name or a reference to the name. Now that that's done, there are several options. You could nest it inside an IF statement, =IF(SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15))5,"Max Adherence Problem",""), or use a chart, or conditional formatting. It really depends on what you're trying to do and your preference. I would use a chart in this example. If your data contains most Database ID's than just DSCHABE, you could use one column for each, and these would be one line on a line graph. Any time the line crossed 5, you would have your problem. "sed" wrote: Call ID Status Type Assigned Resolved Database ID Name 754776 Closed Problem 05/22/08 05/22/08 dschnabe MFGSYS 751502 Closed Problem 05/09/08 05/09/08 dschnabe SAP_MFG 747345 Closed Problem 04/21/08 04/21/08 dschnabe MFGSYS 746533 Closed Problem 04/16/08 04/18/08 dschnabe MFGSYS 746472 Closed Problem 04/16/08 04/17/08 dschnabe SAP_MFG 745730 Closed Problem 04/14/08 04/15/08 dschnabe MFGSYS 741597 Closed Problem 03/26/08 03/27/08 dschnabe SAP_MFG 738441 Closed Problem 03/12/08 03/18/08 dschnabe SAP_MFG 738424 Closed Problem 03/12/08 03/13/08 dschnabe SAP_MFG 737777 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737773 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737271 Closed Problem 03/07/08 03/10/08 dschnabe MFGSYS 737253 Closed Problem 03/07/08 03/07/08 dschnabe MFGSYS 755669 Closed Problem 05/27/08 05/28/08 dschnabe MFGSYS How can I determine if user DSCHNABE has more than 5 CALLID's in the same period of time? ex. if he has 7 call id's open on 03/27/08 then "Max Adherence Problem" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
| Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
| How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions | |||
| =Text(b2-A2,"h") hours between 2 times, what do I do for 10 times | New Users to Excel | |||
| Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) | |||
Linear Mode
