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.
Table of Contents
Method: Average Absolute Values by Formula
Step 1: In D2 enter the formula =AVERAGE(ABS(A2:B8)).
Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get value.
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)).
Step 4: Just press Enter as usual.
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])…