The Excel AVERAGE function returns the average (arithmetic mean) of its arguments.
Sometimes we may want to find out the average of only the positive numbers or negative numbers in a data range that has both positive and negative numbers. The AVERAGE function alone will not accomplish this task.
In this tutorial we will look at 4 methods we can use to find the average of positive or negative numbers of a dataset in Excel.
Method 1: The AVERAGE and IF functions
We can use a formula that combines AVERAGE and IF functions to do this task.
We will use the following dataset to show how this can be done:
Calculate the average of only the positive numbers
To calculate the average of only the positive numbers of the dataset, we do the following:
- Select cell A7 and key in the formula
=AVERAGE(IF(A2:A6>0,A2:A6,””))
- Since this is an array formula, press Ctrl + Shift + Enter to enter the formula. In Excel 365 we press only the Enter key to load the result.:
When we look in the formula bar, we notice that Excel has put the formula in curly brackets:
The curly brackets are how Excel recognizes an array formula. They cannot be entered manually; they can only be produced when we press Ctrl + Shift + Enter.
Explanation of the formula
1 |
=AVERAGE(IF(A2:A6>0,A2:A6,"")) |
- IF(A2:A6>0, A2:A6,””). The IF function checks whether the condition A2:A6>0 is met and returns positive values if it is met and empty strings if it is not met. This results in the array {45, “”,””,””,78}.
- The formula then becomes =AVERAGE({45, “”,””,””,78}) and the average or arithmetic mean value of 61.5 is returned.
Calculate the average of only the negative numbers
To calculate the average of only the negative numbers, we do the following:
- Select cell A7 and type in the formula =AVERAGE(IF(A2:A6<0,A2:A6,””)).
- Since this is an array formula, press Ctrl + Shift + Enter to enter the formula. In Excel 365, we press only the Enter key to load the result.
Explanation of the formula
=AVERAGE(IF(A2:A6<0,A2:A6,””))
- IF(A2:A6<0,A2:A6,””). The IF function checks whether the condition A2:A6<0 is met and returns negative values if it is met and empty strings if it is not met. This results in the array {“”,-56,-98,-56, “”}.
- The formula then becomes =AVERAGE({“”,-56,-98,-56, “”}), and the value -70 is returned.
Method 2: The AVERAGEIF function
We can use the AVERAGEIF function to calculate the average of positive or negative numbers in a data range.
The AVERAGEIF function finds the average or the arithmetic mean for the cells specified by a given condition or criteria.
Calculate the average of only the positive numbers
To calculate the average of only the positive numbers in the data range, we do the following:
- Select cell A7 in our dataset and key in the formula:
1 |
=AVERAGEIF(A2:A6,">0") |
- Press the Enter key:
Calculate the average of only the negative numbers
To calculate the average of only the negative numbers of the dataset we do the following:
- Select cell A7 in our dataset and key in the formula:
1 |
=AVERAGEIF(A2:A6,"<0") |
- Press the Enter key:
Method 3: The SUMIF and COUNTIF functions
We can use the combination of SUMIF and COUNTIF functions to get the average of positive or negative numbers in a dataset.
The SUMIF function adds the cells specified by a given condition or criteria.
The COUNTIF function counts the number of cells within a range that meet the given condition.
Calculate the average of only the positive numbers
We use the following steps:
- Select cell A7 and type in the formula:
1 |
=SUMIF(A2:A6,">0")/COUNTIF(A2:A6,">0") |
- Press the Enter key to load the result:
Calculate the average of only the negative numbers
To calculate the average of only the negative values, we use the same steps as before, except in cell A7 we type in the formula:
1 |
=SUMIF(A2:A6,"<0")/COUNTIF(A2:A6,"<0") |
We then press the Enter key to load the result:
Method 4: AutoFilter and SUBTOTAL function
We can use AutoFilter and the SUBTOTAL function to compute the average of positive or negative numbers in a dataset.
Calculate the average of only the positive numbers
To apply this method to calculate the average of only positive numbers, we use the following steps:
- Select any cell in the data range and press Ctrl + Shift + L or click Data >> Sort & Filter >> Filter to apply AutoFilter:
- Click on the button with the down arrow in the column header row:
- On the shortcut menu select Number Filters and then select the Greater Than comparison:
- Enter the filter criteria and click OK:
- Click Home >> Editing >> AutoSum Arrow >> Average:
- Check the range of cells to be averaged and correct the range address as necessary:
- Press the Enter key to load the result:
Calculate the average of only the negative numbers
To calculate the average of only the negative numbers, we do the following:
- Select any cell in the data range and press Ctrl + Shift + L or click Data >> Sort & Filter >> Filter to apply AutoFilter.
- Click on the button with the down arrow in the column header row.
- On the shortcut menu select Number Filters and then select the Less Than comparison:
- Enter the filter criteria and click OK:
- Click Home >> Editing >> AutoSum Arrow >> Average:
- Check the range of cells to be averaged and correct the range address as necessary:
- Press the Enter key to load the result:
Additional information on the SUBTOTAL function
The SUBTOTAL function returns a subtotal in a list or a database.
If we want to use the SUBTOTAL function in a cell other than the one immediately below the filtered list, we can type in the formula instead of using the AutoSum function.
The first argument in the SUBTOTAL function is a function number that specifies how the subtotal should be calculated. The numbers for specifying the average are 1 or 101:
Conclusion
In this tutorial, we have explored 4 methods that we can use to find the average of positive or negative numbers of a dataset in Excel.
The methods are the use of the AVERAGE and IF functions, applying the AVERAGEIF function, the use of the SUMIF and COUNTIF functions, and the use of the AutoFilter feature and the SUBTOTAL function.