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.

1. Average Absolute Values by Formula

Step1: In D2 enter the formula:

 =AVERAGE(ABS(A2:B8))

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

Step3: 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))

Step4: Just press Enter as usual.

We get the same result.

2. Average Absolute Values with Formula

Now, let’s see the second method in this tutorial. We’ll demonstrate how to calculate the average of absolute values in Excel using a formula.

In a cell where you want to display the average, use the formula:

=AVERAGE(ABS(A2:A8))

where A2:A8 is the range of data you want to calculate the average of absolute values for.

Then press Ctrl+Shift+Enter to change the above formula as array formula to get the result.

3. Video: Average Absolute Values

This Excel tutorial will tackle the task of averaging absolute values. In this video, we’ll explore two methods to calculate the average of absolute values in Excel. One method involves using a helper column, while the other utilizes a formula for a more streamlined approach.

4. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

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