Convert number to negative based on code in neighbor cell

[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?

 

[Peo Sjoblom]

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

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?

 

[Biff]

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

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?

 

[Don Guillett]

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

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?

 

[MAY]

=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

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?

 

[Harlan Grove]

In a third column use

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

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.

 

[b.v.dam]

-----Original Message-----
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?

.
Hello Cindy,

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)