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?