Using Average Time value but exlude 0:00 -- PLease Help

[ccofmars]

Hi to you all,

I thank you in advance for taking the time to help me with this.

I am trying to do average of timevalue but I would like to exclude the cells where the time is 0:00 (h:mm)

the formula I am using is:
{=average(timevalue(right(I6:I3344,7)))}

It does work but unfortunally it also includes all the cells with 0:00 which gives me an average of 0:00 when I need to exclude these cells

how can I exclude 0:00 from the average? thank you thank you thank you ray:

 

[mgirvin]

I don't know why I did not think of this earlier, but if this is a reoccurring problem you might try this:

1) Put your values (time as text) in a column.
2) Create formula with AVERAGE function like: =AVEARGE(ColumnOfTextTime)
3) Highlight column of text times, then use Text To Columns to convert “times as text” to “time as serial number”. (Serial number just means that each time is the proportion of one 24 hour day and is the default setting for time that allows formulas to work on time values). To do this:
i) In Excel 2003 or earlier click on Data menu then on Text to Columns, then click Finished.
ii) In Excel 2007 / 2010 go to Data Ribbon/Tab then in the Data Tools group click on the Text to Columns button, then click Finished.

 

[mgirvin]

ccofmars sent me the data set and the problem was that there was a space in front of all time values so 01:30 was 6 characters instead of 5. I got it to work with a formula similar to barry houdini's:

=AVERAGE(IF(TRIM(B6:B3344)+0>0,TRIM(B6:B3344)+0))

and if there were blanks, which it does not look like the system that is dumping this data produces, then this:

=AVERAGE(IF(B6:B3344<>"",IF(TRIM(B6:B3344)+0>0,TRIM(B6:B3344)+0)))

 

[ccofmars]

OMG!!!! you are my angel! thank you Mike! it works

 

[mgirvin]

I got both barry houdini's and the one that I posted to calculate to the same answer.

I am glad that the formulas helped!

It always helps to know what the data is like!!

 

[ccofmars]

yes they both work!

I did not even realize there were 6 characters instead of 5, so definitely good catch. the file is being exported from a system that is really not user friendly.

I did not really know how to copy/paste the whole data on the discussion board, so it really helped to send you the file directly.

Again thank you all! I could not have done it without you all

have a nice weekend
chantal