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

## How to Use ISDATE Function in Google Sheets

The ISDATE function in Google Sheets is useful if you want to know whether the value in a…

## How to Find Confidence Interval for Population Mean in Excel

This guide will help you understand how to calculate the confidence interval of a population mean in Excel.…

## How to Make an Organization Chart in Google Sheets (Org Chart)

Learning how to make an organization chart in Google Sheets is useful for showing the hierarchy of employees…

## How to Calculate Weighted MAPE in Excel

This guide will explain how to calculate weighted MAPE in Excel. Excel is a powerful tool that allows…

## How to Use CONVERT Function in Google Sheets

The CONVERT function in Google Sheets is useful if you want to convert a number in one measurement…

## How to Use COUPDAYSNC Function in Google Sheets

This guide will discuss how to use the COUPDAYSNC function in Google Sheets. When we need to calculate…