average without 0 value  
Author Message
Tim_S





PostPosted: Tue Nov 09 13:53:15 CST 2004 Top

worksheet functions >> average without 0 value

Hi everybody,

I have a column as follows:
12%
0
10%
0
0
5%
The aver. % will be 9%. But, if we take 0 into consideration it will be 4.5%.

How could I the Average formula without taking this 0 into consideration?

Thanks

Excel470  
 
 
Frank





PostPosted: Tue Nov 09 13:53:15 CST 2004 Top

worksheet functions >> average without 0 value Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<>0,A1:A100))

--
Regards
Frank Kabel
Frankfurt, Germany



> Hi everybody,
>
> I have a column as follows:
> 12%
> 0
> 10%
> 0
> 0
> 5%
> The aver. % will be 9%. But, if we take 0 into consideration it will
be 4.5%.
>
> How could I the Average formula without taking this 0 into
consideration?
>
> Thanks
>

 
 
Domenic





PostPosted: Tue Nov 09 14:01:04 CST 2004 Top

worksheet functions >> average without 0 value
=AVERAGE(IF(A1:A6<>0,A1:A6))

..entered using CONTROL+SHIFT+ENTER.

Hope this helps!

Alex Wrote:
> Hi everybody,
>
> I have a column as follows:
> 12%
> 0
> 10%
> 0
> 0
> 5%
> The aver. % will be 9%. But, if we take 0 into consideration it will b
> 4.5%.
>
> How could I the Average formula without taking this 0 int
> consideration?
>
> Thank

--
Domeni
-----------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=27674

 
 
Alex





PostPosted: Tue Nov 09 14:37:03 CST 2004 Top

worksheet functions >> average without 0 value Thanks, but I'm getting #VALUE!.



>
> =AVERAGE(IF(A1:A6<>0,A1:A6))
>
> ...entered using CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> Alex Wrote:
> > Hi everybody,
> >
> > I have a column as follows:
> > 12%
> > 0
> > 10%
> > 0
> > 0
> > 5%
> > The aver. % will be 9%. But, if we take 0 into consideration it will be
> > 4.5%.
> >
> > How could I the Average formula without taking this 0 into
> > consideration?
> >
> > Thanks
>
>
> --
> Domenic
> ------------------------------------------------------------------------
> Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10785
> View this thread: http://www.excelforum.com/showthread.php?threadid=276747
>
>
 
 
Frank





PostPosted: Tue Nov 09 14:53:16 CST 2004 Top

worksheet functions >> average without 0 value Hi
as said: enter this as array formula with CTRL+SHIFT+ENTER

--
Regards
Frank Kabel
Frankfurt, Germany



> Thanks, but I'm getting #VALUE!.
>

>
> >
> > =AVERAGE(IF(A1:A6<>0,A1:A6))
> >
> > ...entered using CONTROL+SHIFT+ENTER.
> >
> > Hope this helps!
> >
> > Alex Wrote:
> > > Hi everybody,
> > >
> > > I have a column as follows:
> > > 12%
> > > 0
> > > 10%
> > > 0
> > > 0
> > > 5%
> > > The aver. % will be 9%. But, if we take 0 into consideration it
will be
> > > 4.5%.
> > >
> > > How could I the Average formula without taking this 0 into
> > > consideration?
> > >
> > > Thanks
> >
> >
> > --
> > Domenic
>
> ---------------------------------------------------------------------
---
> > Domenic's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=10785
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=276747
> >
> >

 
 
anilsolipuram





PostPosted: Tue Nov 09 15:07:25 CST 2004 Top

worksheet functions >> average without 0 value
try this



=( SUM(A1:A6)/ ( COUNT(A1:A6)-COUNTIF(A1:A6,0) ) )


Let me know if your problem is not resolved


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16271
View this thread: http://www.excelforum.com/showthread.php?threadid=276747