Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 28th 06, 05:50 PM posted to microsoft.public.excel.worksheet.functions
Alex
 
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



  #2   Report Post  
Old March 28th 06, 06:11 PM posted to microsoft.public.excel.worksheet.functions
Gary''s Student
 
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   Report Post  
Old March 28th 06, 06:14 PM posted to microsoft.public.excel.worksheet.functions
Trevor Shuttleworth
 
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   Report Post  
Old March 28th 06, 06:18 PM posted to microsoft.public.excel.worksheet.functions
SteveG
 
Posts: n/a
Default Excluding Zero's from Average (SumIF / CountIF)


You could do this.

=SUMPRODUCT((B18:B65536=B8)*F18:F65536)/SUMPRODUCT((F18:F655360)*(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   Report Post  
Old March 28th 06, 06:27 PM posted to microsoft.public.excel.worksheet.functions
Bob Umlas, Excel MVP
 
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   Report Post  
Old March 28th 06, 07:27 PM posted to microsoft.public.excel.worksheet.functions
Alex
 
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:F655360)*(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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 05:07 PM


All times are GMT +1. The time now is 09:04 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017