Average IF

  • Thread starter Thread starter Fester
  • Start date Start date
F

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
 
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
 
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
 
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.
 
Back
Top