Average cyclic error

[Kent]

Dear all,

I have a series of data in Column A

A
2
5
6
=average(a1:a9)
7

Cell A4 is the average from A1 to A5, in this case is 5
However, if I simply put this formula into A4, it obviously is an error.
What should be the formula then?

Thanks

Kent

 

[Joe User]

Cell A4 is the average from A1 to A5,
in this case is 5[.] However, if I
simply put this formula into A4, it
obviously is an error.
What should be the formula then?

One solution:

=AVERAGE(A1:A3,A5:A9)

But that seems a little too obvious. Is there something about this problem
and your requirements that you have neglected to tell us?


----- original message -----

 

[Ms-Exl-Learner]

If you want to get the Average in A4 Cell then use your Cell Reference like
the below:-

=AVERAGE(A1:A3,A5)
=AVERAGE(A1:A3,A5:A9)
=AVERAGE($A$1:$A$3,$A$5:$A$9)

Remember to Click Yes, if this post helps!

 

[Joe User]

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.

 

[kent]

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