Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Various Ranges
I want to be able to select a various range from one column and
average the numbers. Then, paste the average to a different cell. For example: Select cells F4 thru F12, then average Paste average value in cell J4 Select cells F13 thru F21, then average Paste average value in cel J5 Always the same # of cells to select, and always putting the average in the next cell in the "J" column. I need to do this for about 50 iterations. Can anyone offer some assistance? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Various Ranges
Hi,
Maybe something like this: Sub test() Dim rng As Range Dim rngTarget As Range Dim iCellsCount As Integer Dim i As Integer 'starting cell to be averaged Set rng = Range("F4") 'number of cells to include in average iCellsCount = 9 'starting cell for output Set rngTarget = Range("J4") For i = 1 To 50 If WorksheetFunction.Sum(rng.Resize(iCellsCount)) 0 Then rngTarget.Value = WorksheetFunction.Average(rng.Resize(iCellsCount)) Else rngTarget.Value = 0 End If Set rngTarget = rngTarget.Offset(1) Set rng = rng.Offset(iCellsCount) Next i End Sub You can also do it without VBA. For example, enter this formula in J4 and copy down as needed. =IF(SUM(OFFSET($F$4,(ROW()-4)*9, 0, 9, 1))0, AVERAGE(OFFSET($F$4,(ROW()-4)*9,0,9,1)), 0) -- Hope that helps. Vergel Adriano " wrote: I want to be able to select a various range from one column and average the numbers. Then, paste the average to a different cell. For example: Select cells F4 thru F12, then average Paste average value in cell J4 Select cells F13 thru F21, then average Paste average value in cel J5 Always the same # of cells to select, and always putting the average in the next cell in the "J" column. I need to do this for about 50 iterations. Can anyone offer some assistance? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Built-in for Selecting Ranges? | Excel Programming | |||
Selecting Ranges | Excel Programming | |||
Selecting ranges | Excel Programming | |||
V.B selecting ranges for naming | Excel Programming | |||
selecting ranges | Excel Programming |