Average numbers and ignore zeros
This tutorial shows how to average numbers in a range and ignore zeros through the use of an Excel formula or VBA
Example: Average numbers and ignore zeros
|
=AVERAGEIF(B5:B11,"<>0")
|
This formula uses the Excel AVERAGEIF function with the "<>0" criteria, which means does not equal to zero, to return the average of the numbers captures in the specified range (B5:B11), whilst ignoring cells that contain only a value of zero.
|
Sub Average_numbers_ignore_zeros()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'average numbers ignoring zeros
ws.Range("D5") = Application.WorksheetFunction.AverageIf(Range("B5:B11"), "<>0")
ws.Range("D5") = Application.WorksheetFunction.AverageIf(Range("B5:B11"), "<>0")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range which captures the numbers that you want to average, ignoring zeros, by changing the range reference ("B5:B11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to average the numbers by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range which captures the numbers that you want to average, ignoring zeros, by changing the range reference ("B5:B11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to average the numbers by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to average numbers in a range, that contains zeros, and ignore zeros through the use of an Excel formula or VBA
FORMULA
=AVERAGEIF(range,"<>0")
=AVERAGEIF(range,"<>0")
ARGUMENTS
range: A range of cells which you want to average, ignoring zeros.
range: A range of cells which you want to average, ignoring zeros.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Average values if greater than | How to average numbers that are greater than a specific value using Excel and VBA methods | |
| Average values if less than | How to average numbers that are less than a specific value using Excel and VBA methods | |
| Average only positive numbers | How to average only positive numbers in a specified range using Excel and VBA methods | |
| Average entire column | How to average all values in a single column using Excel and VBA methods | |
| Sum range of cells and ignore errors | How to sum cells and ignore errors using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| AVERAGEIF Function | The Excel AVERAGEIF function returns the average of the numbers in a specified range based on a single criteria |