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?

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?

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?

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?