Average Function and dynamic cell address spartanmba 30.09.2006 15:29:02 I am sure this is an easy answer for some of you, but I am not sure how to do this...thanks for your help in advance. I have an average formula that I would like to dynamically change the number of rows it averages. For example if I have 25 cells of data, but the user only wants ten rows for their work. I would have them enter into a cell the number of points that want averaged and the average formula would dynamically adjust based upon their entry. I know I need to dynamically create an address (row and column) information for the forumla, but I am totally lost on how to do this. THANKS again! John

Re: Average Function and dynamic cell address "Trevor Shuttleworth" <Trevor[ at ]Shucks.demon.co.uk> 30.09.2006 15:35:45 =AVERAGE(OFFSET(A1,,,B1)) Cells start in A1 and the number of rows to be averaged is in cell B1 Regards Trevor "spartanmba" <spartanmba[ at ]discussions.microsoft.com> wrote in message news:96562EAB-D2F1-4521-B3DF-B6F66127B887[ at ]microsoft.com... [Quoted Text] >I am sure this is an easy answer for some of you, but I am not sure how to >do > this...thanks for your help in advance. > > I have an average formula that I would like to dynamically change the > number > of rows it averages. For example if I have 25 cells of data, but the user > only wants ten rows for their work. I would have them enter into a cell > the > number of points that want averaged and the average formula would > dynamically > adjust based upon their entry. I know I need to dynamically create an > address (row and column) information for the forumla, but I am totally > lost > on how to do this. > > THANKS again! > John

Re: Average Function and dynamic cell address "Biff" <biffinpitt[ at ]comcast.net> 30.09.2006 20:24:33 Here's another one: A1:A20 = range of values C1 = number of values to be averaged starting from A1 =AVERAGE(A1:INDEX(A1:A20,C1)) If C1 is empty the formula will calculate the entire range. If no numbers are in the range you'll get a #DIV/0! error. To prevent that error: =IF(COUNT(A1:A20),AVERAGE(A1:INDEX(A1:A20,C1)),"") Biff "spartanmba" <spartanmba[ at ]discussions.microsoft.com> wrote in message news:96562EAB-D2F1-4521-B3DF-B6F66127B887[ at ]microsoft.com... [Quoted Text] >I am sure this is an easy answer for some of you, but I am not sure how to >do > this...thanks for your help in advance. > > I have an average formula that I would like to dynamically change the > number > of rows it averages. For example if I have 25 cells of data, but the user > only wants ten rows for their work. I would have them enter into a cell > the > number of points that want averaged and the average formula would > dynamically > adjust based upon their entry. I know I need to dynamically create an > address (row and column) information for the forumla, but I am totally > lost > on how to do this. > > THANKS again! > John