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

ccofmars

New Member
Joined
May 20, 2010
Messages
44
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 :pray:
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
If you are using Excel 2003 or earlier enter this formula with Ctrl + Shift + Enter (not just Enter):

=AVERAGE(IF(I6:I3344<>0,I6:I3344))

Here is an example:
Excel Workbook
D
80.0625
Sheet3
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.


If you are using Excel 2007 or 2010 try this formula:

=AVERAGEIF(I6:I3344,"<>0")
Excel Workbook
D
90.0625
Sheet3
Excel 2010
Cell Formulas
RangeFormula
D9=AVERAGEIF(I6:I3344,"<>0")
 

ccofmars

New Member
Joined
May 20, 2010
Messages
44
HI Mike, thank you for repsonding. I am using Excel 2003 and I also enter/ctrl/shift to enter my formula.

I see what you mean with your fomula but for whatever reason, because of the formating of the column, when I use =AVERAGE(IF(I6:I3344<>0,I6:I3344)), I get #DIV/0!

i need to be able to include the timevaluefunction in the formula and that would totally work! thank you in advance :)

(hope i make sense :p)
 

ccofmars

New Member
Joined
May 20, 2010
Messages
44
Dont know if I was clear, but I am average time and the cell format in the column is General. so {=average(timevalue(I6:I3344,7)))} works but {=AVERAGE(IF(I6:I3344<>0,I6:I3344))} does not because of the timevalue function i need to add into it.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216

ADVERTISEMENT

If you mean the values are text,

Maybe try this:

=AVERAGE(IF(I6:I3344+0<>0,I6:I3344+0))

Ctrl + Shift + Enter (not just Enter)

The "+0" should take the text times and make them real times.
 

ccofmars

New Member
Joined
May 20, 2010
Messages
44
ok so I tried what you suggested (thank you again by the way :) )
I am getting the following: #VALUE!

to give you an idea, my spreadsheet looks like this:
00:00
00:00
00:00
00:00
00:00
01:15
00:00
00:00
02:33 etc...........

if i right-click on the cells, the format is "general"
and i need to average these 3343 rows (of time hour:minute) and exclude the 00:00.

when i used this formula =average(timevalue(right(I6:I3344,7))) ctrl/shift/enter, it worked fine but the formula includes the 00:00.

I need to find away to be able to be able to average a time value and exclude all the 00:00.

thank you :)
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216

ADVERTISEMENT

Are there AMs and PMs in the column of times? I see 5 characters when you wrote 00:00, 02:00. What is the 7 doing in your formula?
 

ccofmars

New Member
Joined
May 20, 2010
Messages
44
No there are no AMs and PMs in the column of times. Regarding the 7, I got this formula online. Not sure what it is but it works :)
 

ccofmars

New Member
Joined
May 20, 2010
Messages
44
HI Barry

Yes I did use Mike's suggestion and the result was #DIV/0!

Do you know if I can find a way to do an average time value and include a If statement that would exclude all the 00:00 in the column?

thank you
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,836
Messages
5,833,910
Members
430,243
Latest member
madcock83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top