HELP ME PLEASE!!!!! average while ignoring zeros!

tink

New Member
Joined
Feb 28, 2002
Messages
3
Please let me know how to do an average of a group of numbers while ignoring any zeros! the help thing in excel did not work at all. HOW DO YOU DO THIS PLEASE HELP ME!!! I NEED TO KNOW!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
See if these choices are any help.

Thanks to Aladin and Mark W for answering this question many times on this board.

Assuming a range of A1:A10,

Non array formula
=SUM(A1:A10)/MAX(1,SUMPRODUCT((A1:A10<>0)*(ISNUMBER(A1:A10))))

Array (Ctrl+Shift+Enter)
=AVERAGE(IF(A1:A10,A1:A10))

Tom Urtis


Sorry, forgot a shorter non-array from Aladin as well:
=SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,">0"))

T.U.
This message was edited by Tom Urtis on 2002-03-01 20:53
 
Upvote 0
I bow to MrExcel MVP, but would like to ask:

Why do you need the *max* part?

This worked for me:

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")
 
Upvote 0
As noted, these were previous suggestions from others (much more formula-savvy than me), and the first ones I remembered, though both individuals have also posted shorter formula solutions for this question that were not reflected in my reply. Thanks for the tip.

Tom U.
 
Upvote 0
Ok thanks for that, im impressed at the speed of the replies. However now i have another problem. I still want to ignore zeros but now the numbers are still in the same colum, but not all in order like A1:A10 more like A1, A3, A14 etc... How do you do that, i tried seperating them by commas but that didnt seem to work. Thank you.
 
Upvote 0
On 2002-03-01 21:05, Dreamboat wrote:
I bow to MrExcel MVP, but would like to ask:

Why do you need the *max* part?

This worked for me:

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

Consider

{0.00,0.00,"",0.00}

in A1:A4.

Applied to the above set of values

[1]

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

will result in #DIV/0!, while

[2]

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

will compute 0.

The formula in [2] assumes the benign condition that the range of interest does not house negative numbers. To average positive non-zero values, it needs to be rewritten as:

[3]

=SUMIF(A1:A4,">0")/MAX(1,COUNTIF(A1:A4,">0"))

In order to average both neg and pos values excluding zeroes, [2] must be expanded to:

[4]

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0")+COUNTIF(A1:A4,"<0"))

which is exactly eq to the array-formula

[5]

{=AVERAGE(IF(A1:A4,A1:A4))}

By the way, the formula in [5] cannot cope (without modification) with the formula returned blanks in the range of interest, while [4] would.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top