Average smallest n numbers
To average the smallest n numbers in a range you will need to manual input into the formula the smallest n numbers to be averaged
Example: Average smallest n numbers
|
The formula returns the average of the four smallest numbers in range (C8:C14). The formula requires you to manually input the number of smallest numbers that you want to average. In this example we have entered {1, 2, 3, 4} which will average the first, second, third and fourth smallest numbers in the range. If you want to average the five smallest numbers in range (C8:C14) you will need to manually update the formula by replacing {1, 2, 3, 4} with {1, 2, 3, 4, 5}.
|
Sub Average_smallest_n_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'average the four smallest numbers from the specified range
ws.Range("F7").Formula = "=AVERAGE(SMALL(C8:C14,{1,2,3,4}))"
ws.Range("F7").Formula = "=AVERAGE(SMALL(C8:C14,{1,2,3,4}))"
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.
Range: In this example we are averaging the four smallest numbers from range ("C8:C14"). Therefore, if using the exact VBA code, at the very least this range needs to capture all of the values that you want to average.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are averaging the four smallest numbers from range ("C8:C14"). Therefore, if using the exact VBA code, at the very least this range needs to capture all of the values that you want to average.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that captures at the very least the numbers that you want to average by changing the range ("C8:C14") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
n smallest numbers: Select the n smallest numbers to average by changing the reference {1, 2, 3, 4} in the VBA code.
EXPLANATION
To average the smallest n numbers in a range you will need to manual input into the formula the smallest n numbers to be averaged.
To average the smallest n numbers in a range you will need to manual input into the formula the smallest n numbers to be averaged.
FORMULAS
=AVERAGE(SMALL(range,{1, …, n}))
=AVERAGE(SMALL(range,{1, …, n}))
ARGUMENTS
range: The range that captures the numbers to be averaged.
{1, …, n}: Numbers that represents the smallest numbers to be averaged.
RELATED TOPICS
| Related Topics | Description | Related Topics and Description |
|---|---|---|
| Average largest n numbers | To average the largest n numbers in a range you will need to manual input into the formula the largest n numbers to be averaged | |
| Sum largest n numbers | To sum the largest n numbers in a range you can apply two methods, one which requires a manual input, into the formula, of the largest n numbers to be summed and the other can be referenced to a cell | |
| Sum smallest n numbers | To sum the smallest n numbers in a range you can apply two methods, one which requires a manual input, into the formula, of the smallest n numbers to be summed and the other can be referenced to a cell |
RELATED FUNCTIONS
| Related Function | Description | Related Functon and Description |
|---|---|---|
| AVERAGE Function | The Excel AVERAGE function returns the average value from a specified range | |
| SMALL Function | The Excel SMALL function returns the numeric value from a specified range based on the nth smallest position |