Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Zero's from Average (SumIF / CountIF)
Hello everyone,
I was wondering if there was a way of excluding Zeros 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 cant 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 Zeros 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 cant 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 Zeros 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 cant 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Zero's from Average (SumIF / CountIF)
Thanks
Thanks everyone... Steves 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, Non-Consecutive 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) |