Average

  • Thread starter Thread starter Chi
  • Start date Start date
C

Chi

Hi,

I would like to average hours (h:mm format) on column J from cell 2 to cell
150
My formula for Average is =Average IF (J2:J150), "<>0", but the result is
wrong.

Since there are many cells don't have any value yet, I would like to average
the cells have values only.

Thanks
Chi
 
Hi,

Both ARRAY formula
If all your value are positive you can use this

=AVERAGE(IF(J2:J150>0,J2:J150))

or if there could be negative values

=AVERAGE(IF(J2:J150<>"",J2:J50))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Set the format in the results field to TIME, then select the TYPE 30:55:.2
from the format cells dialog box.
 
I am assuming that you are using Excel 2007
try this

=AVERAGEIF(J2:J150,"<>""")

for other Excel version, try this

=AVERAGE(IF(J2:J150<>"",J2:J150,))

format the cell as h:mm
--
Hope this help

Please click the Yes button below if this post have helped answer your needs

Thank You

cheers, francis
 
=AVERAGE(IF(J2:J150<>"",J2:J150,))

That will evaluate empty cells as 0 and include those 0s in the average.

Try it like this...

Array entered:

=AVERAGE(IF(J2:J150<>"",J2:J150))
 
Hi Mike,

Thank you!
Chi

Mike H said:
Hi,

Both ARRAY formula
If all your value are positive you can use this

=AVERAGE(IF(J2:J150>0,J2:J150))

or if there could be negative values

=AVERAGE(IF(J2:J150<>"",J2:J50))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
Click to expand...
 
Thank you!

Francis said:
I am assuming that you are using Excel 2007
try this

=AVERAGEIF(J2:J150,"<>""")

for other Excel version, try this

=AVERAGE(IF(J2:J150<>"",J2:J150,))

format the cell as h:mm
--
Hope this help

Please click the Yes button below if this post have helped answer your needs

Thank You

cheers, francis
Click to expand...
 
Thank you!

T. Valko said:
That will evaluate empty cells as 0 and include those 0s in the average.

Try it like this...

Array entered:

=AVERAGE(IF(J2:J150<>"",J2:J150))

--
Biff
Microsoft Excel MVP





.
Click to expand...
 
Back
Top