How to Average Absolute Values in Excel

We can use AVERAGE function to calculate average of certain values. We can use ABS function to get absolute values for both positive number and negative number. If we want to get the average absolute values, we need to combine both above two functions in the formula. In this free tutorial, we will provide two formulas for you to average absolute values.

Precondition:

Prepare table below. There are some negative numbers.

How to Average Absolute Values in Excel1

Method: Average Absolute Values by Formula


Step 1: In D2 enter the formula =AVERAGE(ABS(A2:B8)).

How to Average Absolute Values in Excel2

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get value.

How to Average Absolute Values in Excel3

Step 3: If you don’t want to enter an array formula and you like apply a regular formula instead, you can enter this formula =AVERAGE(INDEX(ABS(A2:B8),0,0)).

How to Average Absolute Values in Excel4

Step 4: Just press Enter as usual.

How to Average Absolute Values in Excel5

We get the same result.

Related Functions


  • 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],…)….
  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Related Posts

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

Basic Rates Calculation by VLOOKUP Based on Weight Band

Microsoft Excel provides many functions that can execute logical test, search data, return current date and something else. They are very useful in daily work. And Excel VLOOKUP function is one of Excel most frequently used functions. It belongs to ...

Basic Grade Calculation by VLOOKUP Function – Approximate Match

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by ...

Basic Discount Calculation with VLOOKUP Function

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by ...

Basic Usage of INDEX & MATCH – Exact Match
Basic Usage of INDEX & MATCH - Exact Match 1&2

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match or ...

Basic Usage of INDEX & MATCH – Approximate Match

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match by ...

Approximate Match with Multiple Criteria by INDEX & MATCH
Approximate Match with Multiple Criteria by INDEX & MATCH 1 & 2

In Excel, INDEX function and MATCH function are often used together for returning data from specific position. And MATCH function is one of Excel lookup & reference functions that can return approximate value by setting match type. Above all, through ...

How to Generate Random Values by a List in Different Cases in Excel
How to Generate Random Values in Excel10

Sometimes we may create some fake data for testing or some other purpose. If we want to create an amount of numbers, we may feel it is annoying by entering random numbers one by one. For this instance, we can ...

How to Find the First or Last Positive or Negative Number in a Column/List in Excel
How to Find the First or Last Positive or Negative Number in a Column9

Suppose we have a list of data entry, we want to look up the first positive number among them, is there any way to find it out? And if we want to look up the first or last negative number, ...

Sidebar