Convert positive numbers to negative
How to convert positive numbers to negative numbers
Example: Convert positive numbers to negative
|
=IF(B5>0,B5*-1,B5))
|
The formula uses the Excel IF function to test if the number in cell B5 is greater than 0 (positive number). If the test is TRUE the formula will multiply the number in cell B5 by -1, converting a positive number to a negative, 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 positive, therefore, the formula will multiply the number by -1 to convert it to a negative number.
|
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 negative 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 negative 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 negative 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 2).
METHOD 2. Convert positive numbers to negative 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 negative 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 negative. 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 positive numbers to negative 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 negative, therefore, before running this VBA code you need to select a cell or a range of cells that you want to convert from positive to negative numbers.
ADDITIONAL NOTES
Note 1: This VBA code converts the positive numbers to negative and returns the values in the adjacent column.
METHOD 4. Convert positive numbers to negative 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 negative, therefore, before running this VBA code you need to select a cell or a range of cells that you want to convert from positive to negative numbers.
ADDITIONAL NOTES
Note 1: This VBA code converts the positive numbers to negative and returns the values in the selected range, therefore, replacing the numbers that you have selected.
If number > 0 Then
Else
End If
End Function
Note 1: The PosToNeg function will test if a selected number is positive and if TRUE it will return a negative number, otherwise it will return the same number.
Note 2: To apply this function you will need to copy and paste this VBA code into the Excel workbook. This will create the PosToNeg function which only has one parameter, being the cell that captures the value that you want to convert to a negative number.
This tutorial explains how to convert positive numbers to negative numbers by applying Excel and VBA methods. This tutorial also shows how to create your own function that will convert a positive number into a negative number.
Excel Methods: This tutorial provides one Excel method that can be applied to convert positive numbers to negative. It uses the Excel IF Function to test if the selected number is positive and if so the formula will multiply the number by -1, converting it to a negative number. If the test is FALSE, the formula will return the same number (which will be negative).
VBA Methods: This tutorial provides four VBA methods that can be applied to convert positive numbers to negative. The first two methods use the Excel IF function to test if the selected number is positive and if so the formula will multiply the number by -1, converting it to a negative number. If the test is FALSE, the formula will return the same 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 IF function, which is shown in VBA Method 2.
The third and fourth methods convert only the selected numbers. The difference between the third and the fourth method is that the third method returns the converted numbers in the adjacent column, whilst the fourth method replaces the selected values.
Create Function: This tutorial shows how to create a new function that will convert a positive number to negative, by only needing to select a value that you want to convert. The function that we create in this example is called PosToNeg. To do this you will need to create the VBA code first, as per our example, and then you can apply this function into a cell and select the value that you want to convert into a negative number.
=IF(number<0, number*-1, number)
=PosToNeg(number)
ARGUMENTS
number: A numeric value to be converted into a negative number.
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Convert negative numbers to positive | How to convert negative numbers to positive numbers using Excel and VBA methods |