Average
Hi,
I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
Hi,
Both ARRAY formula If all your value are positive you can use this =AVERAGE(IF(J2:J1500,J2:J150)) or if there could be negative values =AVERAGE(IF(J2:J150<"",J2:J50)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
Set the format in the results field to TIME, then select the TYPE 30:55:.2
from the format cells dialog box. "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
=SUMIF(J2:J150,"<0")/COUNTIF(J2:J150,"<0")
Format as desired. -- Best Regards, Luke M "Chi" wrote in message ... Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
I am assuming that you are using Excel 2007
try this =AVERAGEIF(J2:J150,"<""") for other Excel version, try this =AVERAGE(IF(J2:J150<"",J2:J150,)) format the cell as h:mm -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
=AVERAGE(IF(J2:J150<"",J2:J150,))
That will evaluate empty cells as 0 and include those 0s in the average. Try it like this... Array entered: =AVERAGE(IF(J2:J150<"",J2:J150)) -- Biff Microsoft Excel MVP "Francis" wrote in message ... I am assuming that you are using Excel 2007 try this =AVERAGEIF(J2:J150,"<""") for other Excel version, try this =AVERAGE(IF(J2:J150<"",J2:J150,)) format the cell as h:mm -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
Hi Mike,
Thank you! Chi "Mike H" wrote: Hi, Both ARRAY formula If all your value are positive you can use this =AVERAGE(IF(J2:J1500,J2:J150)) or if there could be negative values =AVERAGE(IF(J2:J150<"",J2:J50)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
Thank you!
"petedacook" wrote: Set the format in the results field to TIME, then select the TYPE 30:55:.2 from the format cells dialog box. "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
Thank you!
"Francis" wrote: I am assuming that you are using Excel 2007 try this =AVERAGEIF(J2:J150,"<""") for other Excel version, try this =AVERAGE(IF(J2:J150<"",J2:J150,)) format the cell as h:mm -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi |
Average
Thank you!
"T. Valko" wrote: =AVERAGE(IF(J2:J150<"",J2:J150,)) That will evaluate empty cells as 0 and include those 0s in the average. Try it like this... Array entered: =AVERAGE(IF(J2:J150<"",J2:J150)) -- Biff Microsoft Excel MVP "Francis" wrote in message ... I am assuming that you are using Excel 2007 try this =AVERAGEIF(J2:J150,"<""") for other Excel version, try this =AVERAGE(IF(J2:J150<"",J2:J150,)) format the cell as h:mm -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "Chi" wrote: Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi . |
Average
Hi Luke,
Thank you very much! the formula is wonderful. "Luke M" wrote: =SUMIF(J2:J150,"<0")/COUNTIF(J2:J150,"<0") Format as desired. -- Best Regards, Luke M "Chi" wrote in message ... Hi, I would like to average hours (h:mm format) on column J from cell 2 to cell 150 My formula for Average is =Average IF (J2:J150), "<0", but the result is wrong. Since there are many cells don't have any value yet, I would like to average the cells have values only. Thanks Chi . |
All times are GMT +1. The time now is 09:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com