How to Average Only Positive or Negative Numbers of a Range

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.

EXAMPLE

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])

 

STEPS

nctions. The two functions are used frequently in Excel when running mathematics and logical expression.

FUNCTION INTRODUCTION

a. AVERAGE function returns the average of numbers from a given range reference.

Syntax:

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

c. In I2, enter the formula =AVERAGE(IF(A2:E4<0,A2:E4,””)). Press Ctrl + Shift + Enter to load result.

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],…)….
Related Posts

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Sidebar