Average multiple rows
To average all values in multiple rows you can apply an Excel or a VBA method using the Excel AVERAGE function
Example: Average multiple rows
|
=AVERAGE(5:6)
|
The formula uses the Excel AVERAGE function to average all of the numbers in rows 5 and 6.
|
Sub Average_multiple_rows()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to average all numbers in rows 5 and 6
ws.Range("C8") = Application.WorksheetFunction.Average(ws.Range("5:6"))
ws.Range("C8") = Application.WorksheetFunction.Average(ws.Range("5:6"))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Data Range: Ensure that the data that you want to average from is captured in range ("5:6") in the Analysis worksheet.
Worksheet Name: Have a worksheet named Analysis.
Data Range: Ensure that the data that you want to average from is captured in range ("5:6") in the Analysis worksheet.
ADJUSTABLE PARAMETERS
Date Range: Select the rows that you want to average by changing range ("5:6") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("C8") to any cell in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To average all values in multiple rows you can apply an Excel or a VBA method. The formula used to average values in multiple rows is driven by an Excel AVERAGE function.
In both the VBA and Excel examples the formula averages all of the numbers in rows 5 and 6. This is achieved through the use of the Excel AVERAGE function.
To average all values in multiple rows you can apply an Excel or a VBA method. The formula used to average values in multiple rows is driven by an Excel AVERAGE function.
In both the VBA and Excel examples the formula averages all of the numbers in rows 5 and 6. This is achieved through the use of the Excel AVERAGE function.
FORMULA
=AVERAGE(row_references)
=AVERAGE(row_references)
ARGUMENTS
row_references: The rows which you want to average.
row_references: The rows which you want to average.