Convert positive numbers to negative

How to convert positive numbers to negative numbers

Example: Convert positive numbers to negative

Convert positive numbers to negative

METHOD 1. Convert positive numbers to negative

EXCEL

=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.

METHOD 1. Convert positive numbers to negative using VBA

VBA

Sub Convert_positive_numbers_to_negative()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'convert positive numbers to negative

If ws.Range("B5") > 0 Then

ws.Range("C5") = ws.Range("B5") * -1

Else

ws.Range("C5") = ws.Range("B5")

End If

If ws.Range("B6") > 0 Then

ws.Range("C6") = ws.Range("B6") * -1

Else

ws.Range("C6") = ws.Range("B6")

End If

If ws.Range("B7") > 0 Then

ws.Range("C7") = ws.Range("B7") * -1

Else

ws.Range("C7") = ws.Range("B7")

End If

End Sub

OBJECTS
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.
PREREQUISITES
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: 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

Sub Convert_positive_numbers_to_negative()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'convert negative numbers to positive
For x = 5 To 7

If ws.Range("B" & x) > 0 Then

ws.Range("C" & x) = ws.Range("B" & x) * -1

Else

ws.Range("C" & x) = ws.Range("B" & x)

End If

Next x

End Sub

OBJECTS
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.
PREREQUISITES
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

Sub Convert_positive_numbers_to_negative()
'declare a variable
Dim conv As Range

'convert positive numbers to negative in the selection

For Each conv In Selection
If conv.Value > 0 Then

conv.Offset(0, 1) = -conv.Value

Else

conv.Offset(0, 1) = conv.Value

End If
Next conv

End Sub

PREREQUISITES
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

Sub Convert_positive_numbers_to_negative()
'declare a variable
Dim conv As Range

'convert positive numbers to negative in the selection

For Each conv In Selection
If conv.Value > 0 Then

conv.Value = -conv.Value

End If
Next conv

End Sub

PREREQUISITES
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.

METHOD 1. Create a function to convert positive numbers to negative

CREATE FUNCTION

Function PosToNeg(number As Integer)
'create a new function to convert a positive number to negative

If number > 0 Then

PosToNeg = number * -1

Else

PosToNeg = number

End If

End Function

ADDITIONAL NOTES
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.

Explanation about the formulas used to convert positive numbers to negative

EXPLANATION

DESCRIPTION
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.

FORMULA
=IF(number<0, number*-1, number)
FORMULA (using PosToNeg function)
=PosToNeg(number)

ARGUMENTS
number: A numeric value to be converted into a negative number.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to convert negative numbers to positive numbers using Excel and VBA methods