What is Absolute Value in Excel?

The absolute value of a number is its distance from zero on the number line, regardless of direction, so it’s always a non-negative number. The ABS function in Excel is used to calculate the absolute value in excel of a number.

In other words: the ABS function removes the minus sign (-) from a negative number, making it positive.

Excel VBA Master Tool Kit
  • Syntax : =ABS(number)
  • number: This is the value you want the absolute value for. It can be a specific number, a cell reference, or a formula.

  • Examples:

    • =ABS(-10) will return 10.
    • =ABS(10) will return 10  

The ABS function is useful when you want to ensure that all values are treated as positive, such as in financial models or calculations where negative values aren’t meaningful.

Example 1:- Find Absolute Value in Excel Using ABS Function to

The sample dataset highlights a store’s profit during the first six months of 2023.

To get the absolute results in this dataset:

  • Add a column: Absolute Value.
13.1 abs value
  • Enter the ABS function in C2. The formula is:
  • =ABS(B2)
13.2 abs value
  • Press Enter and the value of C1 cell will change into absolute value (positive number)
Absolute Value in Excel - Simple steps with 4 examples

  • Drag the Fill Handle down to apply the formula to the rest of the cells and see the results.

13.4 abs value
  • All the results will be positive when using the ABS function. It only changes negative numbers to positive ones. Positive numbers and zeros stay the same.

Example 2:- Sum absolute values in Excel

To find the absolute sum of all numbers in a range, you can use one of these formulas:

In the first case, you use an array formula to make the SUM function add all the numbers in the specified range.

  • For an array formula, use: =SUM(ABS(range))
  • After entering the formula, press Ctrl + Shift + Enter to calculate the sum of the absolute values in the specified range.
13.6 abs value
  •  you will find the result.
Absolute Value in Excel - Simple steps with 4 examples
  • In the second case SUMPRODUCT is naturally an array function and can handle a range without any extra steps.
  • For Regular formula use: =SUMPRODUCT(ABS(A2:B5))
13.9 abs value
  • Regular formula, completed with a standard Enter key press.
13.10 abs value
As shown in the screenshots above, both formulas add the absolute values of positive and negative numbers, ignoring the sign.

Example 3:- Find the maximum/minimum absolute value in Excel

 

The simplest way to find the minimum and maximum absolute values in Excel is by using these array formulas:

  • Maximum absolute value: “MAX(ABS(range))”
  • Minimum absolute value: “MIN(ABS(range))”
  • To get the max absolute value: =MAX(ABS(B2:B8))
  • To get the min absolute value: =MIN(ABS(B2:B8))

Remember to complete the array formulas by pressing Ctrl+Shift+Enter.

Absolute Value in Excel - Simple steps with 4 examples

This will give the MAX and MIN absolute value in Excel

Example 4:- Average Absolute values in Excel

The formulas we used to find the min/max absolute values can also be used to average absolute values. Just swap out MAX/MIN with the AVERAGE function

  • Array formula: =AVERAGE(ABS(range))

  • Regular formula: =AVERAGE(INDEX(ABS(range),0,0))

For the sample data in cell B9 and B10 use these formulas:

  • Array formula to average absolute values (press Ctrl + Shift + Enter): =AVERAGE(ABS(B2:B8))

  • Regular formula to average absolute values: =AVERAGE(INDEX(ABS(B2:B8),0,0))

13.12 abs value

Conclusion:-

That’s how to do absolute value in Excel by using the ABS function. The formulas discussed in this tutorial are very straightforward and you will hardly have any difficulties adjusting them for your worksheets.

 To have a closer look, you’re welcome to learn more Excel Functions 

EXCEL FUNCTION – MATCH

EXCEL FUNCTION – INDEX

What is VLOOKUP in Excel?

 

 

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.
Download Practice File

Similar Posts

Advance Filter in Excel – how to create and use
| | |

Advance Filter in Excel – how to create and use

BySumit Shrimal

This guide explains the basics of Excel’s Advanced Filter and shows you how to use it to find records that match one or more complicated conditions.
If you’ve read our previous guide, you know that Excel’s regular filter offers different options for filtering text, numbers, and dates. These options work well for many situations, but not all. When the regular filter isn’t enough, you can use the Advanced Filter to set up custom criteria that fit your exact needs.
Excel’s Advanced Filter is especially useful for finding data based on two or more complex conditions. For example, you can use it to find matches and differences between two columns, filter rows that match another list, or find exact matches with the same uppercase and lowercase letters.
Advanced Filter is available in all Excel versions from 365 to 2003. Click the links below to learn more.

File Manager Tool

File Manager Tool

Byexcelsirji

File Manager tool is an Excel based tool which helps you to delete or move unwanted files from your system. It requires a source and destination folder (in case you want to move files). First it lists all the files available in the folder or sub-folders then you can select the action to be taken for each file such as Move or Delete. With a click of button, tool will take all necessary actions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel VBA Master Tool Kit