Help with averages revisited

[TimJames]

I had posted a question about averages earlier and received a helpful reply,
but after trying it the suggested solution did not work. So after thinking
for a bit this is what I came up with:

I have a series of times in cells B3 through F3, some of which are values of
0:00:00. I wanted to come up with an average for the time worked each day on
my project, so if I work 3 hours a day regardless of how many days I worked,
the average time I worked each day would be 3 hours. So in cell H3 I added
the formula: =(SUM(B3:F3))/(COUNTIF(B3:F3,"<>0:00:00")) and the conditional
format of ISERROR(H3) to color any divide by zero errors the same color of
the background.

This solution is working so far and I wanted to share and welcome any
comments.

Thanks,
Tim

 

[Bob Phillips]

So was the other solution

=AVERAGE(IF(B3:F3<>0,B3:F3))

as an array formula? If so, what was wrong with it?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tyro]

It may help you to know that Excel maintains time as fractions of 24 hours.
For example:

1 second is 1/(24*60*60) = 0.00001157407407407407
1 minute is 1/(24*60) = 0.0006944444444444444
1 hour is 1/24 = 0.04166666666667.

So 01:01:01 is: 0.0423726851851851851851 - The sum of the above.

6 AM is 0.25
12 PM is 0.5

You apply appropriate time formats to the fractions to display them in terms
meaningful to humans.
Excel stores dates as numbers. For example Jan 1, 1900 is day 1. March 7,
2008 is day 39514. March 7, 2008 12 PM is stored as 39514.5

Tyro

 

[TimJames]

In the original reply the formula read:
=AVERAGE(IF(A1:A5<>"",A1:A5))
not
=AVERAGE(IF(B3:F3<>0,B3:F3))
(I did change the cells from A1:A5 to the cells I was using, B3:F3)

when I entered the formula using the Ctrl+Shift+Enter as directed it still
divided by the total number cells regardless of their value. Could the
entries of <>"",B3:F3 versus <>0,B3:F3 (or <>"0:00:00",B3:F3) be the problem?

Thanks,
Tim

 

[Bob Phillips]

It does sound as though the cells are not empty, so maybe best to try

=AVERAGE(IF(B3:F3<>0,B3:F3))

or

=AVERAGE(IF(B3:F3<>TIME(0,0,0),B3:F3))

again as an array formula

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[TimJames]

Thanks Bob,
I tried both of your suggestions and they both worked great! Can you
suggest any sites to learn more about array formulas and their advantage vs
common formulas?

Thanks,
Tim

 

[Bob Phillips]

Not really. Chip has a page at
http://www.cpearson.com/Excel/ArrayFormulas.aspx , but it is just a taster
IMO.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)