| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time [t]:mm:ss
Hi.
I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
Hi!
Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
Thank you so much. That did the trick.
Regards Jan "Biff" skrev i melding ... Hi! Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
You're welcome. Thanks for the feedback!
Biff "Jan G. Thorstensen" wrote in message ... Thank you so much. That did the trick. Regards Jan "Biff" skrev i melding ... Hi! Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
Hi, again. One more problem with this.
When I try to calculate with more arguments, it fails? Say I have different ranges I want to include. =AVERAGE(IF(A2:A6,A10:A140,A2:A6,A10:A14)) or =AVERAGE(IF((A2:A6,A10:A140),(A2:A6,A10:A14))) Now, it returns an error? It say the formula contains too many arguments. How can I avoid this or how should the formula look like? Thank you for your help! Regards Jan. PS I am using the key combination of CTRL,SHIFT,ENTER. "Biff" skrev i melding ... Hi! Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
Hi!
You can't use multiple disjointed ranges in that manner. What do you have in the cells that are between the 2 ranges? A7, A8 and A9? If these cells are empty or have text in them then you can just include them in the reference as a contiguous range like: =AVERAGE(IF(A2:A140,A2:A14)) Text and empty cells will be ignored in the formula. If the cells between the ranges contain numbers then try one of these. The first one is an array. (array entered - CTRL,SHIFT,ENTER) =AVERAGE(IF(CHOOSE({1,2},A2:A6,A10:A14)0,CHOOSE({ 1,2},A2:A6,A10:A14))) Or, normally entered (with just ENTER): =(SUMIF(A2:A6,"0")+SUMIF(A10:A14,"0"))/(COUNTIF(A2:A6,"0")+COUNTIF(A10:A14,"0")) Biff "Jan G. Thorstensen" wrote in message ... Hi, again. One more problem with this. When I try to calculate with more arguments, it fails? Say I have different ranges I want to include. =AVERAGE(IF(A2:A6,A10:A140,A2:A6,A10:A14)) or =AVERAGE(IF((A2:A6,A10:A140),(A2:A6,A10:A14))) Now, it returns an error? It say the formula contains too many arguments. How can I avoid this or how should the formula look like? Thank you for your help! Regards Jan. PS I am using the key combination of CTRL,SHIFT,ENTER. "Biff" skrev i melding ... Hi! Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
Thank you again.
Awesome what is possible to do with formulas. Jan "Biff" skrev i melding ... Hi! You can't use multiple disjointed ranges in that manner. What do you have in the cells that are between the 2 ranges? A7, A8 and A9? If these cells are empty or have text in them then you can just include them in the reference as a contiguous range like: =AVERAGE(IF(A2:A140,A2:A14)) Text and empty cells will be ignored in the formula. If the cells between the ranges contain numbers then try one of these. The first one is an array. (array entered - CTRL,SHIFT,ENTER) =AVERAGE(IF(CHOOSE({1,2},A2:A6,A10:A14)0,CHOOSE({ 1,2},A2:A6,A10:A14))) Or, normally entered (with just ENTER): =(SUMIF(A2:A6,"0")+SUMIF(A10:A14,"0"))/(COUNTIF(A2:A6,"0")+COUNTIF(A10:A14,"0")) Biff "Jan G. Thorstensen" wrote in message ... Hi, again. One more problem with this. When I try to calculate with more arguments, it fails? Say I have different ranges I want to include. =AVERAGE(IF(A2:A6,A10:A140,A2:A6,A10:A14)) or =AVERAGE(IF((A2:A6,A10:A140),(A2:A6,A10:A14))) Now, it returns an error? It say the formula contains too many arguments. How can I avoid this or how should the formula look like? Thank you for your help! Regards Jan. PS I am using the key combination of CTRL,SHIFT,ENTER. "Biff" skrev i melding ... Hi! Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time [t]:mm:ss
You're welcome!
Biff "Jan T." wrote in message ... Thank you again. Awesome what is possible to do with formulas. Jan "Biff" skrev i melding ... Hi! You can't use multiple disjointed ranges in that manner. What do you have in the cells that are between the 2 ranges? A7, A8 and A9? If these cells are empty or have text in them then you can just include them in the reference as a contiguous range like: =AVERAGE(IF(A2:A140,A2:A14)) Text and empty cells will be ignored in the formula. If the cells between the ranges contain numbers then try one of these. The first one is an array. (array entered - CTRL,SHIFT,ENTER) =AVERAGE(IF(CHOOSE({1,2},A2:A6,A10:A14)0,CHOOSE({ 1,2},A2:A6,A10:A14))) Or, normally entered (with just ENTER): =(SUMIF(A2:A6,"0")+SUMIF(A10:A14,"0"))/(COUNTIF(A2:A6,"0")+COUNTIF(A10:A14,"0")) Biff "Jan G. Thorstensen" wrote in message ... Hi, again. One more problem with this. When I try to calculate with more arguments, it fails? Say I have different ranges I want to include. =AVERAGE(IF(A2:A6,A10:A140,A2:A6,A10:A14)) or =AVERAGE(IF((A2:A6,A10:A140),(A2:A6,A10:A14))) Now, it returns an error? It say the formula contains too many arguments. How can I avoid this or how should the formula look like? Thank you for your help! Regards Jan. PS I am using the key combination of CTRL,SHIFT,ENTER. "Biff" skrev i melding ... Hi! Try this: Enter this formula using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A2:A60,A2:A6)) Format the cell as h:mm:ss Biff "Jan G. Thorstensen" wrote in message ... Hi. I try to figure out the average time spent in a range but cant figure out how. This is how it looks like: 00:00:10 00:00:06 00:00:00 00:00:04 00:00:04 ------------ =AVERAGE(A2:A6) =============== Here is a total of 24 seconds. These seconds should be divided with 4 and return the answer; 00:00:06. The problem is: 1) One row does not contain any hours, minutes or seconds and should not be counted. 2) Even when there is no figures in that particular row, the answer returned is wrong? Anybody have a suggestion? Thanks in advance for your help! Jan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding numbers to a Time Formated cell | Excel Discussion (Misc queries) | |||
| Adding time | Excel Worksheet Functions | |||
| what is the formula for adding up time intervals? | Excel Discussion (Misc queries) | |||
| Adding time | New Users to Excel | |||
| adding in time formats | Excel Discussion (Misc queries) | |||
Linear Mode
