Automatically put negative sign in cells with positive numeric dat
 

I have many cells with positive numeric content that I need to change to
negative numbers without changing the absolute value of the numbers. Is
there a way to highlight a block of cells and have a negative sign inserted
in all of them? It will take me forever to do this manually.

"ctdak" wrote in message
...
I have many cells with positive numeric content that I need to change to
negative numbers without changing the absolute value of the numbers. Is
there a way to highlight a block of cells and have a negative sign
inserted
in all of them? It will take me forever to do this manually.


Put -1 (negative 1) in another cell somewhere, and the copy it.
Highlight your data, then click Edit-PasteSpecial. Select Values and also
select the Multiply option.

This will multiply every number in your dataset by -1, which effectively
reverses the sign.
This of course means it wont work if you also have some existing negative
numbers, as those will now become positive.
If this is a problem you can be more selective about which data you reverse,
perhaps only highlighting the numbers which are positive or perhaps use a
filter on your data to display positive rows only.

If the above is not possible (eg you have far to many neg and pos numbers
mixed together to make selecting just the positive ones easily) then try
this.
To only turn positive numbers into negative numbers:
Insert a new worksheet (or use an existing blank one).
If a cell in your new worksheet type the formula:
=if(myDataWorksheet!A1<0,0-myDataWorksheet!A1,myDataWorksheet!A1)
where you should replace myDataWorksheet!A1 with your data worksheet and
first datacell.
Now copy that formula over the matching area of your data.
This formula will copy all your existing data and if its positive it will
reverse the sign. If its negative it will leave it as it is.

You might need some tweaks on this if you have headers or labels etc amongst
your data, but fixing that stuff up will be a lot faster than manually
changing your data. It also has the advantage of not changing your original
data at all.

Hope this helps
A

Enter -1 in an empty cell.

Copy that cell. Select your positives and Paste SpecialMultiplyOKEsc


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 17:42:02 -0700, "ctdak"
wrote:

I have many cells with positive numeric content that I need to change to
negative numbers without changing the absolute value of the numbers. Is
there a way to highlight a block of cells and have a negative sign inserted
in all of them? It will take me forever to do this manually.

Thanks for the response taking various scenarios into account. I got it done.
ctdak


"A C" wrote:


"ctdak" wrote in message
...
I have many cells with positive numeric content that I need to change to
negative numbers without changing the absolute value of the numbers. Is
there a way to highlight a block of cells and have a negative sign
inserted
in all of them? It will take me forever to do this manually.


Put -1 (negative 1) in another cell somewhere, and the copy it.
Highlight your data, then click Edit-PasteSpecial. Select Values and also
select the Multiply option.

This will multiply every number in your dataset by -1, which effectively
reverses the sign.
This of course means it wont work if you also have some existing negative
numbers, as those will now become positive.
If this is a problem you can be more selective about which data you reverse,
perhaps only highlighting the numbers which are positive or perhaps use a
filter on your data to display positive rows only.

If the above is not possible (eg you have far to many neg and pos numbers
mixed together to make selecting just the positive ones easily) then try
this.
To only turn positive numbers into negative numbers:
Insert a new worksheet (or use an existing blank one).
If a cell in your new worksheet type the formula:
=if(myDataWorksheet!A1<0,0-myDataWorksheet!A1,myDataWorksheet!A1)
where you should replace myDataWorksheet!A1 with your data worksheet and
first datacell.
Now copy that formula over the matching area of your data.
This formula will copy all your existing data and if its positive it will
reverse the sign. If its negative it will leave it as it is.

You might need some tweaks on this if you have headers or labels etc amongst
your data, but fixing that stuff up will be a lot faster than manually
changing your data. It also has the advantage of not changing your original
data at all.

Hope this helps
A

Thanks.
Done.

ctdak


"Gord Dibben" wrote:

Enter -1 in an empty cell.

Copy that cell. Select your positives and Paste SpecialMultiplyOKEsc


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 17:42:02 -0700, "ctdak"
wrote:

I have many cells with positive numeric content that I need to change to
negative numbers without changing the absolute value of the numbers. Is
there a way to highlight a block of cells and have a negative sign inserted
in all of them? It will take me forever to do this manually.