SUMIF using value from MOD function

Hello,

I want to conditionally average a range of cells that are formatted in
elapsed time (e.g., 1:03 hrs/min ) but that actually contain a function (
=MOD(G3-E3,1) ). When I refer to these cells containing the MOD functions in
my range (I'm using AVERGEIF), it simply returns my "criteria" value.

Here is my function:

=AVERAGEIF(J3:J17,0.0416666666666667)

where J3:J17 contain the function =MOD(G3-E3,1) , etc., and are formatted in
the hrs/min format. I'm using 0.0416666666666667 as the criteria, because
that is the number for "one hour". In other words, I only want to average
the values that are greater than one hour.

Any ideas why my function isn't working? Or what would be a good way to
conditionally average a range of "times" greater than one hour?

TIA,
Steve

Hi,

try this

=SUMIF(C3:C8,"=0.04")/COUNTIF(C3:C8,"=0.04")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve Vincent" wrote in message
...
Hello,

I want to conditionally average a range of cells that are formatted in
elapsed time (e.g., 1:03 hrs/min ) but that actually contain a function (
=MOD(G3-E3,1) ). When I refer to these cells containing the MOD functions
in
my range (I'm using AVERGEIF), it simply returns my "criteria" value.

Here is my function:

=AVERAGEIF(J3:J17,0.0416666666666667)

where J3:J17 contain the function =MOD(G3-E3,1) , etc., and are formatted
in
the hrs/min format. I'm using 0.0416666666666667 as the criteria, because
that is the number for "one hour". In other words, I only want to average
the values that are greater than one hour.

Any ideas why my function isn't working? Or what would be a good way to
conditionally average a range of "times" greater than one hour?

TIA,
Steve

I only want to average the values
that are greater than one hour.

Try this...

=AVERAGEIF(J3:J17,""&TIME(1,0,0))


--
Biff
Microsoft Excel MVP


"Steve Vincent" wrote in message
...
Hello,

I want to conditionally average a range of cells that are formatted in
elapsed time (e.g., 1:03 hrs/min ) but that actually contain a function (
=MOD(G3-E3,1) ). When I refer to these cells containing the MOD functions
in
my range (I'm using AVERGEIF), it simply returns my "criteria" value.

Here is my function:

=AVERAGEIF(J3:J17,0.0416666666666667)

where J3:J17 contain the function =MOD(G3-E3,1) , etc., and are formatted
in
the hrs/min format. I'm using 0.0416666666666667 as the criteria, because
that is the number for "one hour". In other words, I only want to average
the values that are greater than one hour.

Any ideas why my function isn't working? Or what would be a good way to
conditionally average a range of "times" greater than one hour?

TIA,
Steve