Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable value?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable value?
=SUM(INDIRECT("A1:A" & B1))
-- Gary''s Student - gsnu200730 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable value?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable value?
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable val
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable value?
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL how to change a cell reference in a formula to variable val
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change a tab name with cell reference and formula? | Excel Worksheet Functions | |||
Change the work sheet name in a formula by using cell reference | Excel Worksheet Functions | |||
variable as a cell reference? | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |