Convert negative numbers to positive
How to convert negative numbers to positive numbers
Example: Convert negative numbers to positive
|
=ABS(B5)
|
The formula uses the Excel ABS function to return an absolute number, which in this case would be the positive number of the selected negative number. If the selected number is positive, the Excel ABS function will still return a positive number.
|
METHOD 2. Convert negative numbers to positive using the Excel IF Function
EXCEL
|
=IF(B5<0,B5*-1,B5))
|
The formula uses the Excel IF function to test if the number in cell B5 is less than 0 (negative number). If the test is TRUE the formula will multiply the number in cell B5 by -1, converting the negative number to positive. Alternatively, if the test is FALSE the formula will return the same number as the one captured in cell B5. In this example the number in cell B5 is negative, therefore, the formula will multiply the number by -1 to convert it to a positive number.
|
Dim ws As Worksheet
ws.Range("C5") = Abs(ws.Range("B5"))
ws.Range("C6") = Abs(ws.Range("B6"))
ws.Range("C7") = Abs(ws.Range("B7"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Numbers: If using the same VBA code you need to ensure that the numbers that you want to convert to positive are captured in range ("B5:B7").
Output Range: Select the output range by changing the cell references ("C5"), ("C6") and ("C7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Numbers: Select the numbers which you want to convert to positive by changing range ("B5:B7"), that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: The application of this VBA code is practical in a scenario where there are only limited number of values that you want to convert to positive numbers. You don't want to be typing the same code for each number that you want to convert. Therefore, in a scenario where you want to convert a large range of numbers you can apply the For Loop, which is shown in the following VBA method (Method 2).
METHOD 2. Convert negative numbers to positive using VBA with the ABS Function and For Loop
VBA
Dim ws As Worksheet
For x = 5 To 7
ws.Range("C" & x) = Abs(ws.Range("B" & x))
Next x
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Numbers: If using the exact same VBA code you need to ensure that the numbers that you want to convert to positive are captured in range ("B5:B7").
ADJUSTABLE PARAMETERS
Output Range: In this example we are using the x value to drive the output row number, which has to match the row which captures the number that you want to convert. To change the column in which to return the converted value you need to change the column C reference in the VBA code.
Numbers: In this example we are using the x value to drive the row number of the values which you want to convert to positive. To change the column in which the numbers that you want to convert are captured you will need to change the column B reference in the VBA code.
METHOD 3. Convert negative numbers to positive using VBA with the IF Function
VBA
Dim ws As Worksheet
Else
End If
Else
End If
Else
End If
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Numbers: If using the exact same VBA code you need to ensure that the numbers that you want to convert to positive are captured in range ("B5:B7").
Output Range: Select the output range by changing the cell references ("C5"), ("C6") and ("C7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Numbers: Select the numbers which you want to convert to positive by changing range ("B5:B7").
ADDITIONAL NOTES
Note 1: The application of this VBA code is practical in a scenario where there are only limited number of values that you want to convert to positive numbers. You don't want to be retyping the same code for each number that you want to convert. Therefore, in a scenario where you want to convert a large range of numbers you can apply the For Loop, which is shown in the following VBA method (Method 4).
METHOD 4. Convert negative numbers to positive using VBA with the IF Function and For Loop
VBA
Dim ws As Worksheet
For x = 5 To 7
If ws.Range("B" & x) < 0 Then
Else
End If
Next x
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Numbers: If using the exact same VBA code you need to ensure that the numbers that you want to convert to positive are captured in range ("B5:B7").
ADJUSTABLE PARAMETERS
Output Range: In this example we are using the x value to drive the output row number, which has to match the row which captures the number that you want to convert. To change the column in which to return the converted value you need to change the column C reference in the VBA code.
Numbers: In this example we are using the x value to drive the row number of the values which you want to convert to positive. To change the column in which the numbers that you want to convert are captured you will need to change the column B reference in the VBA code.
METHOD 5. Convert negative numbers to positive using VBA in selected range
VBA
Dim conv As Range
If conv.Value < 0 Then
Else
End If
Next conv
End Sub
Selection: This example converts all of the selected values to positive, therefore, before running this VBA code you need to select a cell or a range of cells that you want to convert from negative to positive numbers.
ADDITIONAL NOTES
Note 1: This VBA code converts the negative numbers to positive and returns the values in the adjacent column.
METHOD 6. Convert negative numbers to positive using VBA in selected range
VBA
Dim conv As Range
If conv.Value < 0 Then
End If
Next conv
End Sub
Selection: This example converts all of the selected values to positive, therefore, before running this VBA code you need to select a cell or a range of cells that you want to convert from negative to positive numbers.
ADDITIONAL NOTES
Note 1: This VBA code converts the negative numbers to positive and returns the values in the selected range, therefore, replacing the numbers that you have selected.
This tutorial explains how to convert negative numbers to positive numbers by applying Excel and VBA methods.
Excel Methods: This tutorial provides two Excel methods that can be applied to convert negative numbers to positive. The first method uses the Excel ABS Function, which returns an absolute number.
The second method uses the Excel IF function to test if the selected number is negative and if so the formula will multiply the number by -1, converting it to a positive number. If the test is FALSE, the formula will return the same number (which will be positive).
VBA Methods: This tutorial provides six VBA methods that can be applied to convert negative numbers to positive. The first two methods use the Abs function, which returns an absolute number. The first method is practical if there are only a limited number of values to convert (e.g. two or three), given we have applied the same code against each of the numbers that we want to convert. However, if you want to convert a large amount of numbers in a range, we can use the For Loop, combined with the Abs function, which is shown in VBA Method 2.
The third and fourth methods use the Excel IF function to test if the selected number is negative and if so the formula will multiply the number by -1, converting it to a positive number. If the test is FALSE, the formula will return the same number. As per above, the third method is practical if there are only a limited number of values to convert (e.g. two or three), given we have applied the same code against each of the numbers that we want to convert. However, if you want to convert a large amount of numbers in a range, we can use the For Loop, combined with the IF function, which is shown in VBA Method 4.
The fifth and sixth methods convert only the selected numbers. The difference between the fifth and the sixth method is that the fifth method returns the converted numbers in the adjacent column, whilst the sixth method replaces the selected values.
=ABS(number)
=IF(number<0, number*-1, number)
ARGUMENTS
number: A numeric value to be converted from into a positive number.
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Convert positive numbers to negative | How to convert positive numbers to negative numbers using Excel and VBA methods |