Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,089
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Determine if Cell Address is within a Range John Michl Excel Worksheet Functions 4 December 22nd 05 08:59 PM
ADDRESS function - dynamic input cell claytorm Excel Discussion (Misc queries) 1 June 28th 05 02:05 PM
Dynamic Function Reference Question excel newbie Excel Discussion (Misc queries) 1 April 20th 05 08:09 PM
Dynamic Range for Function (Vlookup etc) IshtiaqM Excel Worksheet Functions 4 March 27th 05 09:47 PM
dynamic external cell reference bg.itdept Excel Worksheet Functions 4 February 19th 05 04:15 AM


All times are GMT +1. The time now is 01:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"