A useful AVERAGE calculation without using the built-in AVERAGE function

[Sh]

=IF(ISERROR(SUM(B23:M23)/(COUNTIF(B23:M23,">0"))),0,SUM(B23:M23)/(COUNTIF(B23:M23,">0")))


I just wanted to share this for anybody who needed a useful AVERAGE
function that intelligently sums a range of cells but only divides by
the number of cells which contain a value above zero.

This can be useful when calculating running monthly averages over time
in a household account spreadsheet for example.

You will need to change the cell ranges (B23:M23) to suit your own
needs of course...

The built-in AVERAGE function simply adds together the values for a
range of cells and then divides the result by the number of cells in
the range.

This is useful if all of the monthly totals contain valid values, but
if you are only filling in the total values for January and February
then the results of two months divided by 12 is somewhat meaningless.

The heart of this function is:

SUM(B23:M23)/(COUNTIF(B23:M23,">0"))

The SUM part obviously sums the range of cells, the clever part is the
COUNTIF function, which counts up the number of cells that are NOT
zero, and will either return a zero (if all of the cells actually
conatin zero) or a value from one to the maximum number of cells in
the range. This will provide the divisor for the second part of the
average function.

The result of this formula can produce a #DIV0 error if no cells
contain a value above zero, because COUNTIF will return a zero as the
divisor, so I have nested the whole function inside an IF(ISERROR
function to trap the #DIV0 error, which will return a proper zero if
required.

I admit this is somewhat clumsy but it does work, I'm sure a more
experienced EXCEL user could probably find an alternative method or a
tidier method, however my knowledge of Excel is fairly basic and I do
not know how to program any vbscript.

 

[Roger Govier]

Hi

The built-in AVERAGE function simply adds together the values for a
range of cells and then divides the result by the number of cells in
the range.

That is not a correct statement.
It divides by the number of cells in the range that contain a value. If the
cell is Empty, it will ignore it.
In cells A1:A4 enter 1,2,3,4
Leave cell A5 Blank
In cell A6 enter = AVERAGE(A1:A5) and you will see the result 2.5, as the
divisor is 4 (the number of cells with data)

If, however, you enter a value of 0 in cell A5, you will see (correctly) the
result of 2, as Excel has interpreted the value of 0 as a valid result for
that cell and included it within the Average.

You are correct IF your data does contain Zero's and IF you wish to exclude
them.
(I am assuming that your data can never be negative).

Your formula can be written more efficiently as
=SUM(B23:M23)/MAX(1,COUNTIF(B23:M23,">0"))

If the Countif part returns 0, then max will return 1. If all the values in
the range are not >0, then the Sum will be 0 so 0/1 will return 0

--

Regards
Roger Govier

=IF(ISERROR(SUM(B23:M23)/(COUNTIF(B23:M23,">0"))),0,SUM(B23:M23)/(COUNTIF(B23:M23,">0")))


I just wanted to share this for anybody who needed a useful AVERAGE
function that intelligently sums a range of cells but only divides by
the number of cells which contain a value above zero.

This can be useful when calculating running monthly averages over time
in a household account spreadsheet for example.

You will need to change the cell ranges (B23:M23) to suit your own
needs of course...

The built-in AVERAGE function simply adds together the values for a
range of cells and then divides the result by the number of cells in
the range.

This is useful if all of the monthly totals contain valid values, but
if you are only filling in the total values for January and February
then the results of two months divided by 12 is somewhat meaningless.

The heart of this function is:

SUM(B23:M23)/(COUNTIF(B23:M23,">0"))

The SUM part obviously sums the range of cells, the clever part is the
COUNTIF function, which counts up the number of cells that are NOT
zero, and will either return a zero (if all of the cells actually
conatin zero) or a value from one to the maximum number of cells in
the range. This will provide the divisor for the second part of the
average function.

The result of this formula can produce a #DIV0 error if no cells
contain a value above zero, because COUNTIF will return a zero as the
divisor, so I have nested the whole function inside an IF(ISERROR
function to trap the #DIV0 error, which will return a proper zero if
required.

I admit this is somewhat clumsy but it does work, I'm sure a more
experienced EXCEL user could probably find an alternative method or a
tidier method, however my knowledge of Excel is fairly basic and I do
not know how to program any vbscript.

__________ Information from ESET Smart Security, version of virus
signature database 5791 (20110116) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5791 (20110116) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Sh]

Thank you Roger, I knew somebody would have a better knowledge of this
than myself, and thank you for correcting me. I admit my knowledge of
how Excel works is limited, and I was unaware of how the average
function regarded the value of zero as a valid value to include in the
divisor.

Many thanks for the revised formula, I now understand how that works
and it will prove useful as most of my cells contain a zero value and
are not blank. You are also correct that my values are never negative.

Many thanks

 

[קובי]

I would change SUM to SUMIF for consistency (summing the negative
numbers without counting them).