Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function and dynamic cell address
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function and dynamic cell address
=AVERAGE(OFFSET(A1,,,B1))
Cells start in A1 and the number of rows to be averaged is in cell B1 Regards Trevor "spartanmba" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function and dynamic cell address
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine if Cell Address is within a Range | Excel Worksheet Functions | |||
ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) | |||
Dynamic Function Reference Question | Excel Discussion (Misc queries) | |||
Dynamic Range for Function (Vlookup etc) | Excel Worksheet Functions | |||
dynamic external cell reference | Excel Worksheet Functions |