Dear Joe,

As per your advice, I have put the averaged cell on another column, and set
the Iteration to 1. The problem is then sloved

Thank you

Kent



"Joe User" <joeu2004 ...
"Kent" wrote:
Joe is right, the answer =AVERAGE(A1:A3,A5:A9) is simple.
But the location of formula is random but not everytime at A4.
So I want a general formula that always correct when it is put
in other cells, A2, A3.....

Consider this....

In Excel 2003, use Tools Options Calculation to set Iteration to 1.
This permits circular references.

Then, in some cell within the range A1:A100, enter the following array
formula:

=average(if(row(A1:A100)<row(),A1:A100))

PS: I think it is poor design to put the "average" cell within the range
being averaged. It would be better to put the "average" cell before or
after
the range in the same column, or somewhere else entirely. Then you would
not
need to work around the circular reference.

An array formula is entered by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, you should see curly braces around the entire
formula, e.g. {=formula}. If you make a mistake, press F2, edit the
formula
if you wish, then press ctrl+shift+Enter.