getting the average..
what formulas would i use to get and accurate average from a column? we will say column c20:c31. I would like to skip any value that is not greater then zero. ex: 25 I would like that range to show an average of 75... What i am 125 getting is 30.. 0 0 ps- the zero's will change later to actual values so i would like to 0 formulas to count them if they change from 0 to a number. -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
=SUM(C20:C31)/COUNTIF(C20:C31,"0")
Vaya con Dios, Chuck, CABGx3 "fivermsg" wrote: what formulas would i use to get and accurate average from a column? we will say column c20:c31. I would like to skip any value that is not greater then zero. ex: 25 I would like that range to show an average of 75... What i am 125 getting is 30.. 0 0 ps- the zero's will change later to actual values so i would like to 0 formulas to count them if they change from 0 to a number. -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
Try this: =AVERAGE(IF(YourRange0,YourRange)) This is an array function and should be committed with ctrl+shift+enter (not just enter) -- mphell0 ------------------------------------------------------------------------ mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
ok.. both worked.. but now i have dif problem. when i autofilled the formula into my other rows I and O my row C turned.. Now instead of zero's, it displays, #DIV/0! . the formalas no longer work cause it is not zero.. how would you correct this?? -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
=IF(SUM(C20:C31)=0,"",SUM(C20:C31)/COUNTIF(C20:C31,"0")
Vaya con Dios, Chuck, CABGx3 "fivermsg" wrote: ok.. both worked.. but now i have dif problem. when i autofilled the formula into my other rows I and O my row C turned.. Now instead of zero's, it displays, #DIV/0! . the formalas no longer work cause it is not zero.. how would you correct this?? -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
That didn't work.. lets explain it this way, I want the average from c20:c31 excluding #DIV/0! . This is what i see, ex: 100,200,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0! The answer i am getting currently is #DIV/0! . the answer i want is 150. -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
=average(if(isnumber(c20:c31),c20:c31))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column or maybe... =if(count(c2:c31)=0,"No Numbers",average(if(isnumber(c20:c31),c20:c31))) (still array entered) fivermsg wrote: That didn't work.. lets explain it this way, I want the average from c20:c31 excluding #DIV/0! . This is what i see, ex: 100,200,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0! The answer i am getting currently is #DIV/0! . the answer i want is 150. -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 -- Dave Peterson |
getting the average..
can you array merged cells? -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
getting the average..
Try it and see.
Post back with your results. fivermsg wrote: can you array merged cells? -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 -- Dave Peterson |
getting the average..
How about this..........
=SUMIF(C20:C31,"0")/COUNTIF(C20:C31,"0") Vaya con Dios, Chuck, CABGx3 "fivermsg" wrote: That didn't work.. lets explain it this way, I want the average from c20:c31 excluding #DIV/0! . This is what i see, ex: 100,200,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0! The answer i am getting currently is #DIV/0! . the answer i want is 150. -- fivermsg ------------------------------------------------------------------------ fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348 View this thread: http://www.excelforum.com/showthread...hreadid=521051 |
All times are GMT +1. The time now is 09:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com