You can **use the AVERAGEIF function to average only positive or negative numbers in Excel**.

##### Table of Contents

Computing the average of a range of numbers in Excel is quite easy, thanks to the `AVERAGE`

function. However, in cases wherein you only need to average the positive or negative numbers in a given set, this function may not be the ideal solution.

Some people are accustomed to combining the `AVERAGE`

and `IF`

functions to get the answer. Although it’s a fairly decent method, a more efficient solution is using the `AVERAGEIF`

function.

As you can infer from its name, the `AVERAGEIF`

function combines the functionalities of the `AVERAGE`

and `IF`

functions. This function usually returns the average of a set of numbers that meet a given criteria. This means we can also use this function to average only positive or negative numbers within a cell range.

In this guide, you’ll learn how to average only positive or negative numbers in Excel using the `AVERAGEIF`

function. Let’s get started!

**A Real Example of Averaging Only Positive or Negative Numbers**

To appreciate how the `AVERAGEIF`

function works, let’s consider this spreadsheet:

As you can see above, the spreadsheet contains numbers with either positive or negative values. To get the average of all these numbers, you can just simply write a formula and use the `AVERAGE`

function, as shown below.

The `AVERAGE`

function only needs one type of parameter since it only solves for the average, so it’s quite easy to implement it.

However, when averaging only positive or negative values, the `AVERAGE`

function is not enough as it only accepts one parameter. Here is where the `AVERAGEIF`

function can come in handy.

Unlike `AVERAGE`

, the `AVERAGEIF`

function lets you define a condition for the values you want to average. Hence, you can set positive or negative numbers only as the condition of the function.

When used properly, the `AVERAGEIF`

function can help you find the average of either positive or negative numbers only in a cell range, just like what’s shown below.

That’s pretty cool, isn’t it? Now, let’s try out a simple activity for you to learn how to average only positive or negative numbers in Excel.

**Using the AVERAGEIF Function in Excel**

Excel’s `AVERAGEIF`

is actually similar to the AVERAGEIF function in Google Sheets. It’s one of the best options you can use to average only negative or positive values. But before you can use it, obviously, you need to understand its syntax first:

**=AVERAGEIF(range, criteria, [average_range])**

**=**the equal sign is the character we need to type first to initiate a function in Excel.**AVERAGEIF()**this is the name of our function.**range**will hold the cell range that contains the numbers we want to average.**criteria**will hold the condition for the operation we want to perform. This parameter will define whether to solve for the average of positive or negative numbers.**[average_range]**is an optional parameter that can hold the actual cell range we want to average. In case it’s omitted, the**range**parameter will be used instead.

Now that we’ve learned how AVERAGElF works, let’s look at how we can use it in Excel to average only positive or negative numbers.

**How to Average Only Positive Numbers in Excel**

- To follow along with this activity, click the link below to download a copy of our example spreadsheet.

- With the Excel spreadsheet now open, click the cell where you need to display the average of positive numbers. Cell
**H3**is selected in the example below.

- Next, initiate the function by typing in ‘
**=AVERAGEIF(**‘. Be guided by the image below.

- At this point, you can now define the function’s parameters. Set cell range
**A2:E4**as the first parameter (range) of`AVERAGEIF`

.

- After the first parameter, specify the condition or criteria. We want to average only the positive numbers at this point, so indicate ‘
**“>0”**’ on the**criteria**parameter.

- Finish the function by typing close parenthesis ‘
**)**’. Afterward, click anywhere on your spreadsheet or press the**Enter**key on your keyboard to get the average of only the positive numbers in the dataset.

That’s how you average the positive numbers only within a cell range. You can also use the same function to find out the average of negative numbers. It only differs from how the condition is set. Learn how to do that in the next section.

**How to Average Only Negative Numbers in Excel**

- This time, let’s finish up the dataset by solving for the average of negative numbers. We will display the answer on cell
**H4**, so click it on your spreadsheet.

- Next, initiate the
`AVERAGEIF`

function and set**A2:E4**as the range parameter again.

- Since we only want to average the negative numbers this time, we’ll need to define ‘
**“<0”**’ as the criteria parameter.

- Finalize the function and press the
**Enter**key on your keyboard to display the answer.

See how easy that is? Using the `AVERAGEIF`

function in Excel, we’re able to compute the average of negative values only in a dataset.

Great job! You just learned how to use the `AVERAGEIF`

function in Excel to average only the positive or negative numbers in a cell range.

Interested in learning more tricks and functions in Excel? Check out our other articles.

If you would like to receive more useful articles about Excel like this one, just subscribe to our newsletter.