How to change positive values in a cell to negative in Excel 2000

[Mary-Anne]

Say I have a column of numbers and they are positive and I decide that the
sum function would be simpler if they were shown as negative values:

ie 300 in one cell and -250 in another cell gives you an answer of 50. If,
the -250 is shown as 250 how can I change it to -250 to give me the correct
result?

Tedious to retype data.

 

[Dennis]

Type -1 into an empty cell. Copy that cell and then highlight your column of
numbers. Select Edit - Paste special and choose the radio button multiply.

 

[Pete_UK]

Well, you could just change the formula, so instead of something like:

=SUM(A1:B1)

you could make it:

=A1-B1

then just copy it down. Obviously, I'm guessing your formula as you do
not give details.

Hope this helps.

Pete

 

[Mary-Anne]

I tried that, but the answer is wrong. eg 10 rows with 100 in Column 1. Sum
will give answer 1000. If I manually change one x 100 in a cell to -100 the
answer will be 900. Correct answer.

If I use your solution the answer comes to 800?? Don't understand the maths
but I do know the answer is wrong.

 

[Mary-Anne]

If the sum were that simple it would be easy peasy to just change the
formula. However, in a long, complex spreadsheet it becomes really hairy
changing the formula.

I am looking for a neat, easy, quick way of selecting a cell, or multiple
cells, and changing the value to either positive or negative. Possible?

 

[Dennis]

If you have 10 rows at 100 then the sum is 1000. If you want an answer of
900, then you have to add a row at -100. The answer of 800 with my method is
correct because if you change 1 of the rows to -100, you then still only have
10 rows, 9 rows adding up to 900 and 1 row at -100 giving you 800.
If you make all 10 rows -100 which is what your original question stated
then the sum is -1000

 

[Mary-Anne]

Yes! You are right ...it works. However, how do I do the reverse ie change
negative values to positive?

 

[Dennis]

Exactly the same way, a negative number multiplied by -1 will give a positive
value.