Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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


Similar Threads  
Thread  Forum  
Countif cell greater than average  Excel Worksheet Functions  
Average, Excluding Zeros, NonConsecutive Range  Excel Discussion (Misc queries)  
problems with sumif and countif  Excel Discussion (Misc queries)  
Average non continguous cells, excluding zero's  Excel Worksheet Functions  
EXcluding Zeros from the average in a row  Excel Discussion (Misc queries) 