| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
