Excluding Zero's from Average (SumIF / CountIF)

[Alex]

Hello everyone,

I was wondering if there was a way of excluding Zero’s from averages.
I have tried IF and AND but have not been able to get it to work correctly.

I want to be able to fist find column b, then get an average by group where
value in F is not Zero.

This is what I have (it's working, but I can’t seem to be able to count only
if value <> 0, using a countif)

=(SUMIF(B18:B65536,B8,F18:F72))/COUNTIF(B18:B65536,B8)

Sample data:

B C E F G H
Online Sales 329 138 1 150 1
Location Sales 999 18 2 130 1

Thanks

[Gary''s Student]

How about a helper column? AVERAGE() will process zeros, but it ignores
blanks. So in Z1 put =IF(B1=0,"",B1) and copy down.

If B has a value, then so will Z. If B has a zero, then Z will have a
blank. Then average using Z
--
Gary's Student


"Alex" wrote:

> Hello everyone,
>
> I was wondering if there was a way of excluding Zero’s from averages.
> I have tried IF and AND but have not been able to get it to work correctly.
>
> I want to be able to fist find column b, then get an average by group where
> value in F is not Zero.
>
> This is what I have (it's working, but I can’t seem to be able to count only
> if value <> 0, using a countif)
>
> =(SUMIF(B18:B65536,B8,F18:F72))/COUNTIF(B18:B65536,B8)
>
> Sample data:
>
> B C E F G H
> Online Sales 329 138 1 150 1
> Location Sales 999 18 2 130 1
>
> Thanks
>
>

[SteveG]

You could do this.

=SUMPRODUCT((B18:B65536=B8)*F18:F65536)/SUMPRODUCT((F18:F65536>0)*(B18:B65536=B8))

Your ranges need to be the same size. The first sumproduct sums the total where B = B8. The second does the counting.

Does that help?

Steve

[Trevor Shuttleworth]

This isn't quite the answer you are looking for ... but it averages the non
zero values in column B

=(SUMIF(B:B,">0",B:B))/COUNTIF(B:B,">0")

But then, so would: =AVERAGE(B:B) ;-)

Hopefully you can adapt it.

If you need to check a condition in column B and column F you probably need
to use SUMPRODUCT. If you search the archives there are lots of examples.

Regards

Trevor


"Alex" <[email protected]> wrote in message
news:[email protected]...
> Hello everyone,
>
> I was wondering if there was a way of excluding Zero's from averages.
> I have tried IF and AND but have not been able to get it to work
> correctly.
>
> I want to be able to fist find column b, then get an average by group
> where
> value in F is not Zero.
>
> This is what I have (it's working, but I can't seem to be able to count
> only
> if value <> 0, using a countif)
>
> =(SUMIF(B18:B65536,B8,F18:F72))/COUNTIF(B18:B65536,B8)
>
> Sample data:
>
> B C E F G H
> Online Sales 329 138 1 150 1
> Location Sales 999 18 2 130 1
>
> Thanks
>
>

[Bob Umlas, Excel MVP]

If you only include the range without blanks, then this will do:
=SUM(A1:A100)/COUNTIF(A1:A100,"<>0") --(no blanks in A1:A100)
otherwise use this:
=SUM(A1:A100)/SUMPRODUCT(N(A1:A100<>0),N(LEN(A1:A100)>0))

Bob Umlas
Excel MVP

"Alex" wrote:

> Hello everyone,
>
> I was wondering if there was a way of excluding Zero’s from averages.
> I have tried IF and AND but have not been able to get it to work correctly.
>
> I want to be able to fist find column b, then get an average by group where
> value in F is not Zero.
>
> This is what I have (it's working, but I can’t seem to be able to count only
> if value <> 0, using a countif)
>
> =(SUMIF(B18:B65536,B8,F18:F72))/COUNTIF(B18:B65536,B8)
>
> Sample data:
>
> B C E F G H
> Online Sales 329 138 1 150 1
> Location Sales 999 18 2 130 1
>
> Thanks
>
>

[Alex]

Thanks

Thanks everyone...

Steve’s Suggestion worked excellent.

Cheers

Alex

"SteveG" wrote:

>
> You could do this.
>
> =SUMPRODUCT((B18:B65536=B8)*F18:F65536)/SUMPRODUCT((F18:F65536>0)*(B18:B65536=B8))
>
> Your ranges need to be the same size. The first sumproduct sums the
> total where B = B8. The second does the counting.
>
> Does that help?
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=527202
>
>