AVERAGE times not including "00:00" time

djjester

New Member
Joined
Mar 18, 2006
Messages
21
Hey Guys,

I am trying to get a solution that will average a list of times not including the time "00:00"

Here is what I have at the moment which is not working:
A1=0:00
C10=12:51

=AVERAGE(IF(A1<>TIMEVALUE("00:00"),A1,""),IF(C10<>TIMEVALUE("00:00"),C10,""))

I also tried:
=AVERAGE(IF(A1<>TIMEVALUE("00:00"),A1,0),IF(C10<>TIMEVALUE("00:00"),C10,0))

Which evaluates to = 6:25 ie. (0+12:51/2)= 6:25

I also require "AVERAGE" rather than SUM/COUNT as this will need to evaluate the average of a lot of cells that aren't in a list (A1,A2,A3) but are over many sheets and different locations....this will get very messy with SUM/COUNT solutions.

Any help would be great.....im sure it is a simple solution...I just can't find it!!!

Thanks
:-D
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
What is the range of cells you are trying to Average?

For a range entered with Ctrl+Shift+Enter,

=AVERAGE(IF(A1:A10<>"0:00"+0,A1:A10))
 
Upvote 0
Would this help?

=SUM(range)/COUNTIF(Range>0)

Hopefully you'll find something that works.

Cheers

Dave
 
Upvote 0
Thanks brian.....yeah that was what i was trying to get at in the last bit....unfortunately it is not a simple range of cells, rather it is an odd collection of cells over a number of sheets.

Here is the equivalent addition of times:

=Sunday!Q322+Saturday!Q313+Friday!Q304+Thursday!Q295+Wednesday!Q286+Tuesday!Q277+Monday!Q268

As you can see, there is not really a simple order to the cells I require the average for :-(
 
Upvote 0
Hi,

Select Sunday!Q322

Go to Insert > Name > Define

In Names in workbook box type: Sun

click Add.

Do the same for other cells.

To get the average,

=SUM(Sun,Sat,Fri)/SUMPRODUCT(COUNTIF(INDIRECT({"Sun","Sat","Fri"}),"<>"&"00:00"+0))

HTH
 
Upvote 0
Let a range of cells, for example A2:A8, contain Sunday!Q322, Saturday!Q313, Friday!Q304, etc., then try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(INDIRECT(A2:A8))>0,N(INDIRECT(A2:A8))))

Hope this helps!
 
Upvote 0
Hi djjester

Another suggestion.

If you define the values as an array you can use a formula like the one you posted.

(Insert>Name>Define) Define WDayValues as

Code:
=CHOOSE(ROW(!$1:$7),Sunday!$Q$322,Saturday!$Q$313,Friday!$Q$304,Thursday!$Q$295,Wednesday!$Q$286,Tuesday!$Q$277,Monday!$Q$268)

Now you can write the average as:

Code:
=AVERAGE(IF(WDayValues="0:00"+0,"",WDayValues))

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC
 
Upvote 0
Hey guys thanks for all your help.

The solutions posted look like they would work the only thing is that I would like to avoid moving the values into a new range of cells or array prior to calculating averages as this average has to be done for about 300 different ranges.

I would therefore need to make 300 new arrays or move 300 different sets of data...which will be a little painful.

for example here i would need:

=AVERAGE(Sunday!A322,Saturday!A313,Friday!A304,Thursday!A295,Wednesday!A286,Tuesday!A277,Monday!A268 )
=AVERAGE(Sunday!B322,Saturday!B313,Friday!B304,Thursday!B295,Wednesday!B286,Tuesday!B277,Monday!B268 )

then for C,D,E (24 differnt rows) etc. and then 12 different formulas each with 24 rows. (that may sound a bit confusing....but the point is there is a lot of different averages I need to do on the one sheet and the data to be averaged is not in a simple array).

I was really hoping there would be an AVERAGE function that was automatically set to ignore zero values.
:cry:

I will have a play with the solutions posted and see if I can aggregate the data into a new simple array somehow.
 
Upvote 0
You said: I was really hoping there would be an AVERAGE function that was automatically set to ignore zero values.

Me:
I didn't do a whole lot of testing on this, but it should work:


Public Function nzavg(rng As Range) As Double
Dim top As Double
Dim bot As Integer
Dim c As Range
bot = 0
For Each c In rng
If c.Value <> 0 Then
top = top + c.Value
bot = bot + 1
End If
Next
nzavg = top / bot
End Function



Gene, "The Mortgage Man", Klein
 
Upvote 0
Hi again

I was really hoping there would be an AVERAGE function that was automatically set to ignore zero values.

Unfortunately there is not such function. You can however write your own function using vba.

Regards
PGC
 
Upvote 0

Forum statistics

Threads
1,226,433
Messages
6,191,036
Members
453,633
Latest member
mbrooks1009

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