MrExcel Publishing
Your One Stop for Excel Tips & Solutions

inputting negatives


Posted by Chris D on January 31, 2002 2:32 PM

Hi all,

Years ago (like in 1995) my old boss set up a spreadhseet that automatically turned positive numbers into negative numbers as you typed them in...

so you typed "123.45" and it entered them as "-123.45"

Now, I want to do the same in a particular column of this spreadsheet and can't figure how he did it, I'm sure it wasn't a macro as that would have been beyond him in those days....

Can anyone help with a suggestion ?

many thanks
Chris


Posted by Tom Urtis on January 31, 2002 2:37 PM

Try a custom format like
-#,###.00

Posted by Chris D on January 31, 2002 3:18 PM

Perfect ! thanks Tom (nt)

Posted by IML on January 31, 2002 3:28 PM

Be careful

If this is for viewing only this may be fine. But this won't turn the numbers into negatives. If you enter 1 in a cell it will show as -1. Put if you add A1+1, you get 2, not 0 (or if you appear to get -2, change the format of that cell to general)

If this isn't what your after, a non-macro way would be to enter all your numbers, put -1 in a cell, copy it, highlight your range and paste special multiply. Still not exactly what you may be after, but any dependant calculations will be good.

Posted by Chris D on February 02, 2002 3:48 AM

Re: Be careful

thanks IML, I appreciate the caution.

I have this irritatingly lengthy duty each month regarding distribution of tax rebates based on employee and nobody can provide me with the numbers apart from a hard copy print which I then have to type in, and of course they are all credits, so I was after a semi-shortcut.

Not sure I want -ves that are actually +ves though, it would be too easy to forget their proper "polarity" so probably our old favourite paste special multiply/divide will come into play

s'funny - I remember hating that spreadsheet my old boss set up and now it seems I know why, the damn numbers weren't what they seemed ! 6 years later..... !

cheers both
Chris