Insert Average into ranges

[billandrew]

Good afternoon

Trying to insert the worksheet.Function.Average for a specific range of rows

"A2:A9" for columns 5 then "A2:A9 in column 6 and so on. Then there are columns 11 to 14 where I do not wish to insert any average and continue on with columns 15 to 19.

Thank You

 

[billandrew]

Table example below. Hopeful this will provide further explanation to provide an average (red cells) for the range above.

current code = Cells(8, 8).Value = WorksheetFunction.Average(Range(Cells(1, 8), Cells(7, 8)))


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD].71[/TD]
[TD].84[/TD]
[TD].79[/TD]
[TD].47[/TD]
[/TR]
[TR]
[TD].81[/TD]
[TD].86[/TD]
[TD].80[/TD]
[TD].85
[/TD]
[/TR]
[TR]
[TD].78[/TD]
[TD].89[/TD]
[TD].76[/TD]
[TD].60[/TD]
[/TR]
[TR]
[TD].79[/TD]
[TD].90[/TD]
[TD].75[/TD]
[TD].92[/TD]
[/TR]
[TR]
[TD].76[/TD]
[TD].85[/TD]
[TD].71[/TD]
[TD].73[/TD]
[/TR]
[TR]
[TD].69[/TD]
[TD].83[/TD]
[TD].70[/TD]
[TD].90[/TD]
[/TR]
[TR]
[TD].70[/TD]
[TD].84[/TD]
[TD].75[/TD]
[TD].68[/TD]
[/TR]
[TR]
[TD].75[/TD]
[TD].86[/TD]
[TD].75[/TD]
[TD].74[/TD]
[/TR]
</tbody>[/TABLE]

 

[billandrew]

My latest attempt to insert average below row 14 in columns 2 to 6

For i = 2 To 6

'Columns B to F

For rrow = 2 To 14

.Rows 2 to 14

Cells(rrow + 1, i) = WorksheetFunction.Average(Range(Cells(2, i), Cells(rrow, i)))


Next i
Next rrow

 

[Eric W]

You can do that without a loop. Try:

Range("B15:F15").FormulaR1C1 = "=AVERAGE(R[-13]C:R[-1]C)"

which puts a formula in B15:F15 that calculates the average of the rows 2-14 above. If you want the actual value instead of a formula, add this line:

    Range("B8:E8").FormulaR1C1 = "=AVERAGE(R[-7]C:R[-1]C)"
    Range("B8:E8").Value = Range("B8:E8").Value

which converts the formulas to values.

 

[billandrew]

That's works perfect.

Can you explain (R[-7]C:R[-1]C.. If I am not asking too much.

 

[Eric W]

Sure, that's the R1C1 style of referencing. You're probably familiar with the A1, or B3:C5 type of referencing. The letter is the column, the number is the row. Excel has another way to reference a cell, R stands for row, and C stands for column. So R2C3 is the same as C2 (row 2, column 3). If you leave the number off in a formula (there's no number after the C), that means the current row or column. If you put [] around the number, that's the relative row or column.

So in this formula:
Range("B15:F15").FormulaR1C1 = "=AVERAGE(R[-13]C:R[-1]C)"

R[-13]C:R[-1]C means, "Starting in the cell that this formula is placed in (B15), to get to the first cell in this formula, go up 13 rows and stay in this column, and the next cell go up 1 row and stay in this column. You can see that when you define the range that way, the exact same formula will work for all the other cells too, C8:E8. That's a handy VBA trick.

 

[billandrew]

Awesome

I have another range below this one, adjusted the formula, Works Perfect.

Thank You!!