Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cyclic error
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cyclic error
"Kent" wrote:
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 ----- "Kent" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cyclic error
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! -------------------- (Ms-Exl-Learner) -------------------- "Kent" wrote: 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 . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cyclic error
Thanks Ms-Exl-Learner 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..... Kent -- NNTP.hk - (^(oo)^) http://www.nntp.hk/web/ "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Kent" wrote: 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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cyclic error
"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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cyclic error
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I open an excel file if I am getting a cyclic error? | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
How do I correct a cyclic redundancy check error | Excel Discussion (Misc queries) | |||
How do I correct a cyclic redundancy check error | Excel Discussion (Misc queries) | |||
data error - cyclic redundancy check | Excel Discussion (Misc queries) |