Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL Stability Revisited | Excel Discussion (Misc queries) | |||
IF formula within VLOOKUP (revisited) | Excel Discussion (Misc queries) | |||
Problems with autoformatting revisited | Excel Discussion (Misc queries) | |||
Nested If statement revisited | Excel Worksheet Functions | |||
using a macro question revisited | Excel Discussion (Misc queries) |