Removing zero values from an average equation

[Dan012]

Hi everyone,

I am trying to perform an average calculation at the end of a column and my issue is that the column e.g H3:H12 contains zero values as a default from another formula. This is causing the problem where for example the column only contains three real entries (other than zero), instead of diving the sum by three to return the true average it is dividing the sum by 10 (the number of rows in the section) and returning an incorrect answer.

I want to perform the average calculation based only on the cells that have a value greater than zero.

Thanks for any help you can provide.

 

[Trebor76]

Hi there,

Have a look at these solutions from an earlier MrExcel post.

Regards,

Robert

 

[T. Valko]

Hi everyone,

I am trying to perform an average calculation at the end of a column and my issue is that the column e.g H3:H12 contains zero values as a default from another formula. This is causing the problem where for example the column only contains three real entries (other than zero), instead of diving the sum by three to return the true average it is dividing the sum by 10 (the number of rows in the section) and returning an incorrect answer.

I want to perform the average calculation based only on the cells that have a value greater than zero.

Thanks for any help you can provide.

What version of Excel are you using?

Will there be any negative numbers in the range?

 

[Dan012]

Thank you for your help, i managed to find the solution from the previous posts. Thanks again.

 

[Aladin Akyurek]

Hi everyone,

I am trying to perform an average calculation at the end of a column and my issue is that the column e.g H3:H12 contains zero values as a default from another formula. This is causing the problem where for example the column only contains three real entries (other than zero), instead of diving the sum by three to return the true average it is dividing the sum by 10 (the number of rows in the section) and returning an incorrect answer.

I want to perform the average calculation based only on the cells that have a value greater than zero.

Thanks for any help you can provide.

In addition to the links Trebor76 quotes:

If on Excel 2007 or later, you can invoke:

=AVERAGEIF(H3:H12,">0",H3:H12)

Or, in case there would be no values above 0's...

=IFERROR(AVERAGEIF(H3:H12,">0",H3:H12),0)

 

[Trebor76]

In addition to the links Trebor76 quotes:

Which Aladin has also provided the solution to!!

 

[T. Valko]

In addition to the links Trebor76 quotes:

If on Excel 2007 or later, you can invoke:

=AVERAGEIF(H3:H12,">0",H3:H12)

Or, in case there would be no values above 0's...

=IFERROR(AVERAGEIF(H3:H12,">0",H3:H12),0)

When the range and the average_range are the same there's no need to repeat it.

=AVERAGEIF(H3:H12,">0")

=IFERROR(AVERAGEIF(H3:H12,">0"),0)