XL how to change a cell reference in a formula to variable value?

[Bernard]

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?

 

[Gary''s Student]

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

 

[Dave Peterson]

Another one:

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

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?

 

[Ron Coderre]

Try something like this:

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

Where:
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))

Where:
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

 

[Ron Coderre]

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

 

[ShaneDevenshire]

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.

 

[Bernard]

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