making a # value positive or negative based on contents of another

[Guest]

How do I make the numerical value of column "a" postive or negative based on
the text value of column "b"

For example:

$126,513.00 c
$5,616,156.00 d
$132.00 d
$156.00 d
$55,444.00 c

When I upload the data all numbers come up positive. I would like it to
make it so the number comes up negative if column b contains a "c" and
positive if it contains a "d". I am willing to have to create a function in
row c and have the numbers in "a" move to column "c".

 

[Pete_UK]

Put this formula in C1, then:

=IF(B1="c",-A1,A1)

and copy down (double-click the fill handle [the small black square in
the bottom right corner of the cursor] with C1 selected).

If you want to put the values back in column A, then highlight the
cells in C, click <copy> then Edit | Paste Special | Values (check) |
OK then <Enter>. This will have fixed the values, so that you can now
copy them over the original values in column A and delete column C.

Hope this helps.

Pete

 

[Guest]

perfect!

Put this formula in C1, then:

=IF(B1="c",-A1,A1)

and copy down (double-click the fill handle [the small black square in
the bottom right corner of the cursor] with C1 selected).

If you want to put the values back in column A, then highlight the
cells in C, click <copy> then Edit | Paste Special | Values (check) |
OK then <Enter>. This will have fixed the values, so that you can now
copy them over the original values in column A and delete column C.

Hope this helps.

Pete

How do I make the numerical value of column "a" postive or negative based on
the text value of column "b"

For example:

$126,513.00 c
$5,616,156.00 d
$132.00 d
$156.00 d
$55,444.00 c

When I upload the data all numbers come up positive. I would like it to
make it so the number comes up negative if column b contains a "c" and
positive if it contains a "d". I am willing to have to create a function in
row c and have the numbers in "a" move to column "c".

 

[Pete_UK]

Thanks for the feedback.

Pete

perfect!

Put this formula in C1, then:

=IF(B1="c",-A1,A1)

and copy down (double-click the fill handle [the small black square in
the bottom right corner of the cursor] with C1 selected).

If you want to put the values back in column A, then highlight the
cells in C, click <copy> then Edit | Paste Special | Values (check) |
OK then <Enter>. This will have fixed the values, so that you can now
copy them over the original values in column A and delete column C.

Hope this helps.

- Show quoted text -