A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excluding Zero's from Average (SumIF / CountIF)



 
 
Thread Tools Display Modes
  #1  
Old March 28th 06, 05:50 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)

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


Ads
  #2  
Old March 28th 06, 06:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)

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
>
>

  #3  
Old March 28th 06, 06:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)

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" > wrote in message
...
> 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
>
>



  #4  
Old March 28th 06, 06:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)


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

  #5  
Old March 28th 06, 06:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)

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
>
>

  #6  
Old March 28th 06, 07:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)

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
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif cell greater than average Mary Ann Excel Worksheet Functions 4 August 10th 05 09:49 AM
Average, Excluding Zeros, Non-Consecutive Range Coal Miner Excel Discussion (Misc queries) 9 August 4th 05 10:21 PM
problems with sumif and countif Simon Shaw Excel Discussion (Misc queries) 6 July 23rd 05 10:02 PM
Average non continguous cells, excluding zero's Keithlearn Excel Worksheet Functions 2 April 27th 05 01:22 PM
EXcluding Zeros from the average in a row Geo Excel Discussion (Misc queries) 4 December 31st 04 04:07 PM


All times are GMT +1. The time now is 11:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.