Average IF

[Fester]

I have data in two columns.

Column E Column H
9:45 :19
12:40 :05
15:54 :10


I want to average column H if Column E is less than 11:59 (so in this
case, the answer would be :19),

and subsequently average if it is greater than 11:59 075)

Brendon

 

[Bernie Deitrick]

Fester,

=SUMIF(E:E,"<0.5",H:H)/COUNTIF(E:E,"<0.5")
=SUMIF(E:E,">=0.5",H:H)/COUNTIF(E:E,">=0.5")

HTH,
Bernie
MS Excel MVP

 

[Shane Devenshire]

Hi,

In 2007

=AVERAGEIF(A1:A8,">3",B1:B8)

In 2003

=AVERAGE(IF(A1:A8>3,B1:B8,""))

This formula must be array entered - press Shift+Ctrl+Enter, instead of enter
or
=SUMIF(A1:A8,">3",B1:B8)/COUNTIF(A1:A8,">3")

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Fester]

OK, thanks guys that worked.

 

[gunthr]

thanks boys. i was having a hard time weeding out avereging numbers and
exclusing zeros in sets of data between Excel 2000 and Excel 2007. I have
never used the shift+ctrl+enter function before. if you would like to tell
me more of the capabilities of using this function, i would appreciate it. i
was recently laid off, and i have spent quite a bit of time building a
template to track my taxes between jobs in the meantime. if interested, a
copy can be yours.

thanks.