ABS Function – Absolute Value in Excel, VBA, Google Sheets

This tutorial demonstrates how to use the Excel ABS Function in Excel to calculate the absolute value.

ABS Function Overview
The ABS Function Calculates the absolute value of a number. The absolute value is the number’s distance from zero. Example: The absolute value of -9 is 9.

To use the ABS Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

ABS Function Syntax and Inputs:

=ABS(number)

number – A number.

Creating a absolute value graph

To create a cosine curve in Excel, we need to first choose our start and end points and then list out a lot of numbers. Let’s go from -1 up to 1 in increments of 0.1.

=ABS(C3)

Next, we’re going to add the 0.1 onto the angle and then calculate the cosine of that angle. Use the formula

=C3+$G$2

(the $ signs lock G2 so the formula will always reference that 0.1 even if we copy the formula!)

Now highlight both the new angle and cosine function that we’ve calculated, hold the handle, and drag it down until our angle reaches 1.

If you chose “Scatter with Smooth Lines”, you might notice the graph is a bit wobbly close to (0,0), and it does not create a sharp point as expected at x=0. This is because of our chosen increment of 0.1. To fix this, either use a smaller increment like 0.01 or choose “Scatter with Straight Lines”.

ABS in Google Sheets

The ABS Function works exactly the same in Google Sheets as in Excel:

Highlight the entire range of both angles and cosines, click insert, find the graphs and select “Scatter with Straight Lines”

ABS Examples in VBA

You can also use ABS in VBA. This example will loop through cells A2:A4 and output the absolute value in cells B2:B4.

Sub Abs_Example1()
    Dim cell As Range
    
    For Each cell In Range("A2:A4")
        cell.Offset(0, 1) = Abs(cell.Value)
    Next cell
End Sub

The result will be as following.(please see B2:B4)

The following 2 examples both will return 12.

MsgBox Abs(-12)

MsgBox Abs(12)

To find a number closest to 2 when a number array (1.5, 3.1, 2.1, 2.2, 1.8) is given, you can use the following code.

Sub Abs_Example2()
    Dim Numbers
    Dim item
    Dim closestValue As Double
    Dim diff As Double
    Dim minDiff As Double
    minDiff = 100
    
    Numbers = Array(1.5, 3.1, 2.1, 2.2, 1.8)
        
    For Each item In Numbers
        diff = Abs(item - 2)
        If diff < minDiff Then
            minDiff = diff
            closestValue = item
        End If
    Next item

    MsgBox "The closest value: " & closestValue
End Sub

The result will be 2.1 as following.