Opposite of ABS Function in Excel (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you need to convert any negative values into positive ones then you may use the ABS function in Excel. But, I will explain today how to use the opposite of the ABS function in Excel. Which means that I’m going to convert any values into negative one.


Opposite of ABS Function in Excel: 5 Examples

Here, I’m going to demonstrate 5 suitable methods of how to use the opposite of the ABS function in Excel. For your better understanding, I will use a sample dataset. Which has 2 columns. Those are Categories and Expenses\Income. The sample dataset is given below.

Dataset for Using Opposite of ABS Function in Excel


1. Use of Subtraction Operator with ABS Function in Excel

Here, you know that the ABS function will return all absolute values or positive values. So, if you use a Minus sign (-) before the ABS function then all the positive values will convert into a negative one. Now, let’s talk about the steps.

Steps:

  • Firstly, you have to select a blank cell D5 where you want to keep the result.
  • Secondly, you should use the corresponding formula in the D5 cell.
=-ABS(C5)

Here, in this formula, the ABS function will convert the C5 cell value into an absolute one and then the Minus sign (-) will again convert it into a negative one.

Use of Minus with ABS Function as Opposite of ABS Function in Excel

  • Subsequently, you must press ENTER to get the result.

Finally, you will see the converted negative values.

  • Now, I will copy the same formula using relative cell references. To do this, select cell D5. A square box will be shown in the bottom-right corner of cell D5, it is called the Fill Handle icon. Click the Fill Handle icon, hold it, and drag until you reach cell D13.
  • Then, release the Mouse cursor.

Lastly, you will get all the converted negative values.

Result of using Minus before ABS function as Opposite in Excel

Read More: How to Sum Absolute Value in Excel


2. Applying ABS Function & Multiplication Operator

Here, you can apply the Multiplication operator (*) with the ABS function for getting the opposite result which will give the ABS function in Excel. Now, let’s talk about the steps.

Steps:

  • Firstly, you have to select a blank cell D5 where you want to keep the result.
  • Secondly, you should use the corresponding formula in the D5 cell.
=ABS(C5)*-1

Here, in this formula, the ABS function will convert the C5 cell value into an absolute one and then I have multiplied the output with -1 to convert it into a negative one.

apply Multiplication operator with ABS function for getting opposite result in Excel

  • After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13. Or you can double-click on the Fill Handle icon.

Finally, you will get all the converted negative values.

Read More: How to Get Absolute Value in Excel


3. Employing IF Function in Excel

You can apply the IF function to find out the opposite result instead of using the ABS function in Excel. Furthermore, you can modify this function for different types of results according to your preference. The steps are given below.

Steps:

  • Firstly, you have to select a blank cell D5 where you want to keep the result.
  • Secondly, you should use the corresponding formula in the D5 cell.
=IF(C5<0,C5,C5*-1)

Employing IF Function in Excel as Opposite of ABS Function

Formula Breakdown

Here, the IF function will return a logical test result.

  • Firstly, C5<0 denotes the logical test. This test will check whether the cell value of C5 is less than 0 or not.
  • Secondly, C5—>  when the cell value is less than 0 which means the logic is true then it will return the cell value of C5.
  • Lastly, C5*-1 —> when the cell value is greater than 0 then it will do this operation. In this operation, the cell value will multiply with -1.

  • After that, you must press ENTER to get the result.
  • Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.

Lastly, you will get all the converted negative values.


4. Multiplying with Negative 1

You can apply the Multiplication (*) operator as the opposite of the ABS function in Excel. Here, with this formula, you can convert the positive values into negative ones. On the other hand, the negative values will turn into positive ones. The steps are given below.

Steps:

  • Firstly, you have to select a different cell D5 where you want to see the result.
  • Secondly, you should use the following formula in the D5 cell.
=C5*-1

Here, the cell value of C5 will multiply with -1.

  • Subsequently, you must press ENTER to get the result.

Use of Multiplication operator as the opposite of ABS function

  • After that, you can double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.

At this time, you will get the following result. Here, if you notice deeply then you can see that the positive values of the C column become negative and the negative values become positive.

Read More: Changing Negative Numbers to Positive in Excel


5. Use of VBA to Get Opposite of ABS Function

You can employ the VBA code for getting the opposite result which you will get by using the ABS function in Excel. The steps are given below.

Steps:

  • Firstly, you have to choose the Developer tab >> then select Visual Basic.

Use of VBA in Excel as Opposite of ABS function

  • Now, from the Insert tab >> you have to select Module.

  • After that, write down the following Code in the Module.
Sub Opposite_ABS()
    Dim my_cell_value As Range
    For Each my_cell_value In Range("C5:C13")
        my_cell_value.Offset(0, 1) = -Abs(my_cell_value.Value)
    Next my_cell_value
End Sub

Code Breakdown

  • Here, I have created a Sub Procedure named Opposite_ABS.
  • Next, I have declared a variable my_cell_value as Range.
  • After that, I used a For Each Loop to apply the calculation to all defined cells.

  • Now, Save the code then go back to Excel File.
  • Then, from the Developer tab >> you have to select Macros.

dialog box of Macro will appear.

  • Now, you need to select Macro (Opposite_ABS) and click on Run.

At this time, you can see the converted negative values.


💬 Things to Remember

  • All the methods will convert any values into negative ones except method 4. Which will convert the negative to positive and positive to negative.

Practice Section

Now, you can practice the explained method by yourself.

Practice Section of how to use Opposite of ABS Function in Excel


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 5 methods of how to use the opposite of ABS function in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Excel ABS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo