Errors

[Alex Hammerstein]

Hi

I am setting up a spreadsheet for a year in advance and some cells calculate
averages. However for those cells with nothing in I am getting div/o error.
I.e. In cell E7: =IF(ISERR(E7),0,AVERAGE(E8:E10)), but that is producing a
circular ref.

Is there a way that I can get rid of these div/o error messages?

Thanks

A

 

[Bob Greenblatt]

Hi

I am setting up a spreadsheet for a year in advance and some cells calculate
averages. However for those cells with nothing in I am getting div/o error.
I.e. In cell E7: =IF(ISERR(E7),0,AVERAGE(E8:E10)), but that is producing a
circular ref.

Is there a way that I can get rid of these div/o error messages?

Thanks

A

Average will not calculate empty cells. Don't use zer, and you should not
get errors.

 

[CyberTaz]

As Bob G replied, if there is at least one value in the range being averaged
rather than all empty cells the error won't be generated. In some cases
storing zeroes is undesirable so here's one alternative that fixes what you
had a good start on. Enter this in E7:

=IF(ISERROR(AVERAGE(E8:E10)),"No Data",AVERAGE(E8:E10))

The formula in a cell can't refer to the cell it's in -- that's where your
circular reference came from -- but it can refer to the *operation*

Note that the "No Data" text string can be replaced by any string you
prefer. You could also replace it with "" to leave the cell blank, or a
value such as a zero.

 

[JE McGimpsey]

As Bob G replied, if there is at least one value in the range being averaged
rather than all empty cells the error won't be generated. In some cases
storing zeroes is undesirable so here's one alternative that fixes what you
had a good start on. Enter this in E7:

=IF(ISERROR(AVERAGE(E8:E10)),"No Data",AVERAGE(E8:E10))

The formula in a cell can't refer to the cell it's in -- that's where your
circular reference came from -- but it can refer to the *operation*

Note that the "No Data" text string can be replaced by any string you
prefer. You could also replace it with "" to leave the cell blank, or a
value such as a zero.

While ISERROR(AVERAGE(...)) works, it has two drawbacks.

1) It will mask errors other than the #DIV/0 produced by
AVERAGE() on an empty range. So if you had a #VALUE! error
in the range, you'd see "No Data", which isn't true, and may
lead to incorrect follow-on calculations.

2) It uses 2 functions where a single one would do, calculating
the average twice if there's no error.

Better:

=IF(COUNT(E8:E10) > 0, AVERAGE(E8:E10), "No Data")

or, equivalently and implicitly:


=IF(COUNT(E8:E10), AVERAGE(E8:E10), "No Data")

 

[Alex Hammerstein]

Very many thanks for your kind help - these forums are brilliant, I learn so
much!

Alex