Help with averages revisited
 

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

Help with averages revisited
 

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)



"TimJames" wrote in message
...
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

Help with averages revisited
 

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" wrote in message
...
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

Help with averages revisited
 

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" wrote:

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)



"TimJames" wrote in message
...
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

Help with averages revisited
 

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" wrote in message
...
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" wrote:

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)



"TimJames" wrote in message
...
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

Help with averages revisited
 

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" wrote:

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" wrote in message
...
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" wrote:

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)



"TimJames" wrote in message
...
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

Help with averages revisited
 

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)



"TimJames" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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)



"TimJames" wrote in message
...
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