software

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: www.excelforum.com/member.php...oamp;userid=32348
View this thread: www.excelforum.com/showthread...hreadid=521051=SUM(C20:C31)/COUNTIF(C20:C31,quot;gt;0quot;)

Vaya con Dios,
Chuck, CABGx3
quot;fivermsgquot; wrote:

gt;
gt; what formulas would i use to get and accurate average from a column? we
gt; will say column c20:c31. I would like to skip any value that is not
gt; greater then zero.
gt;
gt; ex: 25 I would like that range to show an average of 75... What i
gt; am
gt; 125 getting is 30..
gt; 0
gt; 0 ps- the zero's will change later to actual values so i
gt; would like to
gt; 0 formulas to count them if they change from 0 to a number.
gt;
gt;
gt; --
gt; fivermsg
gt; ------------------------------------------------------------------------
gt; fivermsg's Profile: www.excelforum.com/member.php...oamp;userid=32348
gt; View this thread: www.excelforum.com/showthread...hreadid=521051
gt;
gt;


Try this:

=AVERAGE(IF(YourRangegt;0,YourRange))

This is an array function and should be committed with ctrl shift enter
(not just enter)--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=521051
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: www.excelforum.com/member.php...oamp;userid=32348
View this thread: www.excelforum.com/showthread...hreadid=521051=IF(SUM(C20:C31)=0,quot;quot;,SUM(C20:C31)/COUNTIF(C20:C31,quot;gt;0quot;)

Vaya con Dios,
Chuck, CABGx3quot;fivermsgquot; wrote:

gt;
gt; ok.. both worked.. but now i have dif problem. when i autofilled the
gt; formula into my other rows I and O my row C turned.. Now instead of
gt; zero's, it displays, #DIV/0! . the formalas no longer work cause it is
gt; not zero.. how would you correct this??
gt;
gt;
gt; --
gt; fivermsg
gt; ------------------------------------------------------------------------
gt; fivermsg's Profile: www.excelforum.com/member.php...oamp;userid=32348
gt; View this thread: www.excelforum.com/showthread...hreadid=521051
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=32348
View this thread: www.excelforum.com/showthread...hreadid=521051=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,quot;No Numbersquot;,average(if(isnumber(c20:c31),c20:c31)))
(still array entered)

fivermsg wrote:
gt;
gt; That didn't work.. lets explain it this way, I want the average from
gt; c20:c31 excluding #DIV/0! .
gt;
gt; This is what i see,
gt; ex: 100,200,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!
gt;
gt; The answer i am getting currently is #DIV/0! . the answer i want is
gt; 150.
gt;
gt; --
gt; fivermsg
gt; ------------------------------------------------------------------------
gt; fivermsg's Profile: www.excelforum.com/member.php...oamp;userid=32348
gt; View this thread: www.excelforum.com/showthread...hreadid=521051

--

Dave Peterson


can you array merged cells?--
fivermsg
------------------------------------------------------------------------
fivermsg's Profile: www.excelforum.com/member.php...oamp;userid=32348
View this thread: www.excelforum.com/showthread...hreadid=521051Try it and see.

Post back with your results.

fivermsg wrote:
gt;
gt; can you array merged cells?
gt;
gt; --
gt; fivermsg
gt; ------------------------------------------------------------------------
gt; fivermsg's Profile: www.excelforum.com/member.php...oamp;userid=32348
gt; View this thread: www.excelforum.com/showthread...hreadid=521051

--

Dave Peterson

How about this..........

=SUMIF(C20:C31,quot;gt;0quot;)/COUNTIF(C20:C31,quot;gt;0quot;)

Vaya con Dios,
Chuck, CABGx3
quot;fivermsgquot; wrote:

gt;
gt; That didn't work.. lets explain it this way, I want the average from
gt; c20:c31 excluding #DIV/0! .
gt;
gt; This is what i see,
gt; ex: 100,200,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!
gt;
gt; The answer i am getting currently is #DIV/0! . the answer i want is
gt; 150.
gt;
gt;
gt; --
gt; fivermsg
gt; ------------------------------------------------------------------------
gt; fivermsg's Profile: www.excelforum.com/member.php...oamp;userid=32348
gt; View this thread: www.excelforum.com/showthread...hreadid=521051
gt;
gt;