# How to Average Only Positive or Negative Numbers in Excel

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

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

2. 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. 3. Next, initiate the function by typing in ‘=AVERAGEIF(‘. Be guided by the image below. 4. At this point, you can now define the function’s parameters. Set cell range A2:E4 as the first parameter (range) of `AVERAGEIF`. 5. 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. 6. 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

1. 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. 2. Next, initiate the `AVERAGEIF` function and set A2:E4 as the range parameter again. 3. Since we only want to average the negative numbers this time, we’ll need to define ‘“<0”’ as the criteria parameter. 4. 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. ### Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

##### You May Also Like ## How to Use MARGINOFERROR Function in Google Sheets

This guide will discuss how to use MARGINOFERRORfunction in Google Sheets.  When we want to determine if there… ## How to Fix the #REF! Error in Excel

This guide will explain the best methods you can use to fix the #REF! error in Excel. The… ## How to Fix Formula Not Updating Automatically in Excel

This tutorial will explain a few ways to fix formulas not updating automatically in Microsoft Excel. By default,… ## How to Calculate CAGR in Google Sheets

The CAGR or compound annual growth rate is a helpful metric in investment analysis that gives the real…  