This post will guide you on how to average only positive or negative numbers of a range in Excel 2013/2016/2019/365.

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative numbers ignored. In this article, we will help you to construct a formula with AVERAGE and IF functions to get average of only positive numbers or negative numbers.

Refer to above left side table, we can see both positive numbers and negative numbers are listed in range A2:E4. We want to calculate average of positive numbers and negative numbers separately and save results in H2 and I2 correspondingly.

In this instance, we will enter below formula into H2. We build this formula with AVERAGE and IF function.

=IF(logical_test,[value_if_true],[value_if_false])

1. Average Only Positive Numbers of a Range

The two functions are used frequently in Excel when running mathematics and logical expression. The AVERAGE function returns the average of numbers from a given range reference. The IF function returns “true value” or “false value” based on the result of provided logical test. It is one of the most popular function in Excel.

In cell H2, enter the formula:

=AVERAGE(IF(A2:E4>0,A2:E4,""))

Range reference A2:E4 represents all numbers in this range.

This formula will execute IF function firstly to filter and keep all positive numbers in current array. If numbers are greater than 0, they are positive numbers. If logical expression “A2:E4>0” is true (number in A2:E4 is greater than 0), this number will be saved in array A2:E4.

After comparing each number in range A2:E4 with 0, below numbers are filtered and saved. Then AVERAGE function will calculate the average of these numbers.

After entering the formula, press Ctrl + Shift + Enter to load result because this is an array formula. But on Excel 365 you can directly press Enter as usual to load result.

2. Average Only Negative Numbers of a Range

In I2, enter the formula:

=AVERAGE(IF(A2:E4<0,A2:E4,""))

Press Ctrl + Shift + Enter to load result.

3. Video: Average Only Positive or Negative Numbers of a Range in Excel

This video will demonstrate how to easily calculate the average of only positive or negative numbers within a range in Excel.

4. Related Functions

• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
• Excel AVERAGE function
  The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….