Convert number to negative based on code in neighbor cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two coloumns - one alpha, and one a numeric value. The first column contains either a "D" or a "C" and indicates whether the corresponding number in the adjacent column should be positive or negative. I would like to convert the numbers to negative, where needed, based on the code in the first column. Is there any way to do this with a formula?
 
In a third column use

=IF(A2="D",C2*-1,C2)

copy down (adjust the formula to fit your conditions)

Once you are done, copy the help column, do edit>paste special as values in
place

--

Regards,

Peo Sjoblom


Cindy B. said:
I have two coloumns - one alpha, and one a numeric value. The first
Click to expand...
column contains either a "D" or a "C" and indicates whether the
corresponding number in the adjacent column should be positive or negative.
I would like to convert the numbers to negative, where needed, based on the
code in the first column. Is there any way to do this with a formula?
 
Hi Cindy,

Try this:

=IF(A1="D",B1*-1,B1)

Just swap the code letter if I guessed wrong!

Biff
-----Original Message-----
I have two coloumns - one alpha, and one a numeric
Click to expand...
value. The first column contains either a "D" or a "C"
and indicates whether the corresponding number in the
adjacent column should be positive or negative. I would
like to convert the numbers to negative, where needed,
based on the code in the first column. Is there any way
to do this with a formula?
 
One way. Right click on sheet tab>view code>insert this
As written it is working on column M which is col 13 for col N

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If UCase(Target) = "D" Then _
Target.Offset(, 1).Value = _
Abs(Target.Offset(, 1))
If UCase(Target) = "C" And _
Left(Target.Offset(, 1), 1) <> "-" _
Then Target.Offset(, 1).Value = _
"-" & Target.Offset(, 1)
End Sub

Cindy B. said:
I have two coloumns - one alpha, and one a numeric value. The first
Click to expand...
column contains either a "D" or a "C" and indicates whether the
corresponding number in the adjacent column should be positive or negative.
I would like to convert the numbers to negative, where needed, based on the
code in the first column. Is there any way to do this with a formula?
 
=IF(A1="C",B1*-1,B1)
Assuming column A is where the D or C is, column B is
where your amount is, and column C is where you want the
result to end up.
-----Original Message-----
I have two coloumns - one alpha, and one a numeric
Click to expand...
value. The first column contains either a "D" or a "C"
and indicates whether the corresponding number in the
adjacent column should be positive or negative. I would
like to convert the numbers to negative, where needed,
based on the code in the first column. Is there any way
to do this with a formula?
 
Peo Sjoblom said:
In a third column use

=IF(A2="D",C2*-1,C2)
Click to expand...

So many responces using '*-1'. I had to pick one to which to respond.

How about the alternative

=IF(A2="D",-C2,C2)

Unary minus generally beats multiplying by -1.
 
-----Original Message-----
I have two coloumns - one alpha, and one a numeric
Click to expand...
value. The first column contains either a "D" or a "C"
and indicates whether the corresponding number in the
adjacent column should be positive or negative. I would
like to convert the numbers to negative, where needed,
based on the code in the first column. Is there any way
to do this with a formula?
.
Hello Cindy,
Click to expand...


Maybe this works:

D 2 -2
C 5 5
C 4 4
D 3 -3


The formula in the 3rd column is: =IF(A19="D";(B19*-
1);B19) So the first D is in cell A19 (there were already
some cells filled above).

If you mean something else just e-mail me

Success,

Bert - (e-mail address removed)
 
Back
Top