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.

Tutorial Content

## 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.