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.
nctions. The two functions are used frequently in Excel when running mathematics and logical expression.
a. AVERAGE function returns the average of numbers from a given range reference.
=AVERAGE(number1, [number2], …)
b. 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.
Syntax:a. 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.
b. 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.
- 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],…)….