I want to change a cell range in a formula to be a variable reference. This
is to allow calculation of a moving average which is selectable. Example
change the moving average from calculation over 10 values (A1:A10) to
calculation over 50 values (A1:A50), without having to go in and change the
formula. In this example the variable value would be 10 to 50 (in say B1).
any ideas?

=SUM(INDIRECT("A1:A" & B1))

--
Gary''s Student - gsnu200730

Another one:

=SUM(OFFSET(A1,0,0,B1,1))



Bernard wrote:

I want to change a cell range in a formula to be a variable reference. This
is to allow calculation of a moving average which is selectable. Example
change the moving average from calculation over 10 values (A1:A10) to
calculation over 50 values (A1:A50), without having to go in and change the
formula. In this example the variable value would be 10 to 50 (in say B1).
any ideas?

--

Dave Peterson

Nice one Dave. This is the simple but v. effective.
Bernard.

"Dave Peterson" wrote:

Another one:

=SUM(OFFSET(A1,0,0,B1,1))



Bernard wrote:

I want to change a cell range in a formula to be a variable reference. This
is to allow calculation of a moving average which is selectable. Example
change the moving average from calculation over 10 values (A1:A10) to
calculation over 50 values (A1:A50), without having to go in and change the
formula. In this example the variable value would be 10 to 50 (in say B1).
any ideas?

--

Dave Peterson

Try something like this:

=AVERAGE(A1:INDEX(A:A,B1))

Whe
B1 contains the number of cells to include in the average.

Alternatively,
you could indicate the start cell and the number of cells to include with
this:
=AVERAGE(INDEX(A:A,B1):INDEX(A:A,B1+B2-1))

Whe
B1 contains the starting cell row number
B2 contains the number of cells to include in the average

For example
If B1 is 101 and B2 is 5
The formula would calculate the average of cells A11:A15

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bernard" wrote:

I want to change a cell range in a formula to be a variable reference. This
is to allow calculation of a moving average which is selectable. Example
change the moving average from calculation over 10 values (A1:A10) to
calculation over 50 values (A1:A50), without having to go in and change the
formula. In this example the variable value would be 10 to 50 (in say B1).
any ideas?

Drat! Typo!

This part:
If B1 is 101 and B2 is 5

should be this
If B1 is 11 and B2 is 5

(I'm torn between blaming my fingers for typing it or my eyes for not
catching it)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

=AVERAGE(A1:INDEX(A:A,B1))

Whe
B1 contains the number of cells to include in the average.

Alternatively,
you could indicate the start cell and the number of cells to include with
this:
=AVERAGE(INDEX(A:A,B1):INDEX(A:A,B1+B2-1))

Whe
B1 contains the starting cell row number
B2 contains the number of cells to include in the average

For example
If B1 is 101 and B2 is 5
The formula would calculate the average of cells A11:A15

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bernard" wrote:

I want to change a cell range in a formula to be a variable reference. This
is to allow calculation of a moving average which is selectable. Example
change the moving average from calculation over 10 values (A1:A10) to
calculation over 50 values (A1:A50), without having to go in and change the
formula. In this example the variable value would be 10 to 50 (in say B1).
any ideas?

Hi,

I'm sure you can replace the SUM suggestions with AVERAGE equivalents. But
here is a shorter variation:

=AVERAGE(OFFSET(A1,,,B1))

And of course you could highlight the range and see the AVG on the Status Bar.

And here is a rather cute idea to display the average of the current
selection in a formula:
1. Create the following macro

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Names.Add "s", Target
End Sub

2. Create the following function

Function MovAvg(R As Range)
On Error GoTo err1
MovAvg = WorksheetFunction.Average(R)
Exit Function
err1:
MovAvg = 0
End Function


3. in the spreadsheet enter =MovAvg(s)
You will get an error, but ignore it.

4. Select any range of numbers.
--
Cheers,
Shane Devenshire


"Bernard" wrote:

I want to change a cell range in a formula to be a variable reference. This
is to allow calculation of a moving average which is selectable. Example
change the moving average from calculation over 10 values (A1:A10) to
calculation over 50 values (A1:A50), without having to go in and change the
formula. In this example the variable value would be 10 to 50 (in say B1).
any ideas?