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

Sidebar