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

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<0,A1:A100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Alex" schrieb im Newsbeitrag
...
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

=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...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=276747

Thanks, but I'm getting #VALUE!.

"Domenic" wrote:


=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...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=276747

Hi
as said: enter this as array formula with CTRL+SHIFT+ENTER

--
Regards
Frank Kabel
Frankfurt, Germany

"Alex" schrieb im Newsbeitrag
...
Thanks, but I'm getting #VALUE!.

"Domenic" wrote:


=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...o&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=276747

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...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=276747