what formulas would i use to get and accurate average from a column? we will say row c20:c31. I would like to skip any value that is not greater then zero.
example, row displays: 25,125,0,0,0
I would like that range to show an average of 75... What i am getting is 30..
ps- the zero's will change later to actual values so i would like to formulas to count them if they change from 0 to a number.
Last edited by fivermsg; 03-10-2006 at 10:12 AM. Reason: doesn't look right
=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
>
>
Try this:
=AVERAGE(IF(YourRange>0,YourRange))
This is an array function and should be committed with ctrl+shift+enter (not just enter)
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??
=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
>
>
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.
=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
=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
can you array merged cells?
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
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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks