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:
- = 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
AVERAGEIFfunction 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.
