Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF problems
Hello again all,
I'm WAY over my head on this, but... I am trying to calc the average age of a issue ticket. Open column C is the SEVERITY LEVEL. Open column D is the time created, example 10/09/2006 10:16:47. This formula works (in Excel it has { } around it. =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 I am trying to replace NOW() with Cell C1. C1 is a static date and time, example 10/10/06 10:40 AM I have this formula, but it returns values that are WAY off. Thoughts? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF problems
Sorry,
It may be helpful to put the values I am getting, and formula's i'm using. Column D has only one value, and it is 10/09/2006 10:16:47 Cell C1 has the value of 10/10/06 10:40 AM The value returned from formula =((C1)-(SUMIF(Open!C1:C816,B16,Open!D1:D816)/COUNTIF(Open!C1:C816,B16)))*24 is 936010.6667 The value returned from formula =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 is 26.51808889 "David" wrote: Hello again all, I'm WAY over my head on this, but... I am trying to calc the average age of a issue ticket. Open column C is the SEVERITY LEVEL. Open column D is the time created, example 10/09/2006 10:16:47. This formula works (in Excel it has { } around it. =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 I am trying to replace NOW() with Cell C1. C1 is a static date and time, example 10/10/06 10:40 AM I have this formula, but it returns values that are WAY off. Thoughts? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF problems
Your result is consistent with the SUMIF/COUNTIF part of the formula
returning zero as C1*24 will be equal to approx 936011. I note you have different ranges, have you excluded the one qualifying date? Note: your formula isn't an array formula, it doesn't require { and }. If you want an array formula to do the same.... =(C1-AVERAGE(IF(open!C1:C983=B16,open!D1:D983)))*24 "David" wrote: Sorry, It may be helpful to put the values I am getting, and formula's i'm using. Column D has only one value, and it is 10/09/2006 10:16:47 Cell C1 has the value of 10/10/06 10:40 AM The value returned from formula =((C1)-(SUMIF(Open!C1:C816,B16,Open!D1:D816)/COUNTIF(Open!C1:C816,B16)))*24 is 936010.6667 The value returned from formula =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 is 26.51808889 "David" wrote: Hello again all, I'm WAY over my head on this, but... I am trying to calc the average age of a issue ticket. Open column C is the SEVERITY LEVEL. Open column D is the time created, example 10/09/2006 10:16:47. This formula works (in Excel it has { } around it. =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 I am trying to replace NOW() with Cell C1. C1 is a static date and time, example 10/10/06 10:40 AM I have this formula, but it returns values that are WAY off. Thoughts? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF problems
Thank you for the response.
Based on your post, i used your formula, but inconsistant results. With real data, it was close to the actual, but not correct result. So I populated the open tab with sample data. Open tab rows 2 thru 16, column C with the number 1, column D with 10/10/2006 15:52 on the tab with the formula, cell c1 value is 10/10/06 5:00 PM, cell b16 is the number 1. Here is a manual intensive formula that works. =(C1-(AVERAGE(Open!D2:D16)))*24 value returned is 1.1 hours I am looking for a formula that will evaluate the open tab, column C for the matching value in cell b16 and then pull in the value of column D (open tab). Cell C1 minus the Average all the values of column D (open tab), then multiply by 24. Here is the formula you created, but it returns the value of 936017.0 VERY ODD thing does occur, i click on the cell that has the value 936017.0 I then click on fx next to tool bar, a dialog box titled function Argument appears. In this dialog box, it says Formul result = 1.1 hours. Why can I not get it to display 1.1 hours like is displayed in the functional argument dialog box? Sorry for the long ol story, but hopefully it all makes sense. David "daddylonglegs" wrote: Your result is consistent with the SUMIF/COUNTIF part of the formula returning zero as C1*24 will be equal to approx 936011. I note you have different ranges, have you excluded the one qualifying date? Note: your formula isn't an array formula, it doesn't require { and }. If you want an array formula to do the same.... =(C1-AVERAGE(IF(open!C1:C983=B16,open!D1:D983)))*24 "David" wrote: Sorry, It may be helpful to put the values I am getting, and formula's i'm using. Column D has only one value, and it is 10/09/2006 10:16:47 Cell C1 has the value of 10/10/06 10:40 AM The value returned from formula =((C1)-(SUMIF(Open!C1:C816,B16,Open!D1:D816)/COUNTIF(Open!C1:C816,B16)))*24 is 936010.6667 The value returned from formula =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 is 26.51808889 "David" wrote: Hello again all, I'm WAY over my head on this, but... I am trying to calc the average age of a issue ticket. Open column C is the SEVERITY LEVEL. Open column D is the time created, example 10/09/2006 10:16:47. This formula works (in Excel it has { } around it. =(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1:D983)/COUNTIF(Open!C1:C983,B16)))*24 I am trying to replace NOW() with Cell C1. C1 is a static date and time, example 10/10/06 10:40 AM I have this formula, but it returns values that are WAY off. Thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect and Sumif Syntax Problems | Excel Discussion (Misc queries) | |||
Problems with SUMIF() | Excel Worksheet Functions | |||
problems with sumif and countif | Excel Discussion (Misc queries) | |||
Sumif Problems | Excel Discussion (Misc queries) | |||
sumif alphanumeric problems | Excel Discussion (Misc queries) |