Showing plus sign on positive numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a formatting problem about how to display positive numbers as +.50 and negative numbers as -.50

I use Excel 2000 to show commodities prices. I show the last traded price, the previous price, and then the change. To get that change, i have a simple formula in the cell: =last-pre
The number is always in dollars and cents, but I dont want 0.00 to show, I want just plain 0

I want my positive numbers to show up with a plus sign, to show more clearly that the price went up. So I made a custom format: +#.##;-#.##;
Well that works... BUT.... if the change is +1.00 it shows as +1. without its zeros. That's no good, so I made another custom format: +#.##00;-#.##00;0
Well that works... BUT.... if the change is +.30 it shows as +.300 with too many zeros. That's no good, so I made another custom format: +#.##0;-#.##0;0

So twice a day, I am changing the formats by hand on this darn change column... :

Do you see where I am going with this? Are there any programmers out there who might have a simple code that will automatically format my cells to show positive and negative numbers with the signs, AND the correct number of zeros?

Thank you for your assistance
Beth
 
OHMYGOD that's too simple! But damn if it didn't work on every scenario
I never tried that
+#.00;-#.00;0
I thought the # signs had to go where I didn't want zeros... ::rolls eyes:

You have saved me alot of work - Thank you VERY MUCH !
 
Back
Top