[Solved] cell number sign reversal

[canino]

I have a very long column of dollar numbers that are all positive. I"m looking for a way to reverse the sign to a negative dollar value. I have tried to use the IF function to generate the negative number in another column which works fine but I need to make that negative number an absolute value so I can copy it back into the column containing the positive dollar amount. Or, if there is a simpler way to just convert the column with the positive dollar amount to a negative dollar amount that is copyable to another sheet without any formulas.

I have been busting my head over this off and on for several days and have not been able to find a solution.

Thanks in advance for any help.

Jim

[FJCC]

If your numbers are in column A, you can use a formula like the following in column B to reverse the sign.

Code: Select all

=-1 * A2

You can then copy all of the cells in column B and select the menu Edit -> Paste special to paste only the numbers and not the formulas back into column A. Alternatively, you can leave the values in column A in place and use column B for whatever purpose requires a negative number.

[canino]

I must have done something wrong because it did indeed reverse the sign in the new column but it left the formula in the new column and when I attempted to delete the old column, it all fell apart.????

[FJCC]

You can't just delete the formulas in column A. You have to paste the information from column B back into A. Select the cells in column B, copy them (menu Edit -> Copy), select the cells in column A, and Paste Special (menu Edit -> Paste Special), choosing to paste only the numbers. After that, column A should contain numbers, not formulas and then you can delete the formulas in column B.

It is generally better to keep both columns when you transform data. You can hide column A if you don't want it in the way.

[keme]

A different approach would be "paste special" using an operation. This way, you modify all values directly, without inserting any formulas.

• Type -1 into an empty cell (this is your temporary workspace)
• Copy that cell to the clipboard (ctrl+C will do)
• Delete the temporary -1 value
• Select the range of cells where sign should be reversed
• Edit - Paste special (or ctrl+shift+V)
• In the Selection section of that "paste special" dialog, tick numbers and untick everything else
• In the Operation section, select Multiply.
• OK

You think it is a long list of operations? It is really not as complicated as it may look.

[canino]

Want to thank everyone. Problem solved and I am now 'smarter' than before.
Jim