I’m graphing some temperatures and need to display them with the degree symbol, but they need to remain numbers (not text) so they can be graphed. Here’s the number format:
+###°C;-###°C;_+0°C
The output looks like this:
60 +60°C
0 0°C
-20 -20°C
The three parts of the number format are separated by semicolons. The first part defines the format for positive numbers, the second part for negative numbers, and the third part for zero. The second and third parts are optional, as is a fourth part that defines the format for text.
To make the degree symbol, hold down the Alt key and type 0176 on the numeric keypad.
In the third part, I left enough space for a plus sign. To leave space, I include an underscore followed by the character whose width is the amount of space I want. I did this so that if the numbers are left aligned the C’s would line up. They don’t line up because for a couple of reasons. First, there can be multiple digits in the positive and negative numbers. Second, the plus sign and minus sign take up take up a different amount of space in Tahoma.
I’d like to come up with a format that lines up the C’s regardless of the cell alignment. I’d even be willing to limit the temperatures to -99 to +99. I assume this has will use conditions, but I couldn’t figure it out. Any ideas?
Dick –
Seems to work:
+###°C;_###°C;_+_00°C
…Michael
That’s two minuses in a row in the middle.
…mrt
I like a bit more space between the “+” or “-” and the number. As well, if the last digit in the format is a “#” then fractional degrees that round to zero render as “+ °C”.
Try:
+ ##0°C;_- ##0°C;_+_00°C
and that’s still two minuses in the middle….
Doesn’t get to 0° Kelvin in Dcarno’s experiments…
There I was, sitting in my urologist’s waiting room, and I realized that my format (and now D’s) break for single non-zero digit (-9 to +9) temps.
So, with leading zeros:
+ 00°C;_- -00°C;_+_00°C
Constrained -99->+99, and two minuses in the middle.
…mrt
For the minus sign, us an en-dash (alt+0150 on the numeric keypad). It’s very close in width to the plus sign.
Or use Courier.
Convert the temperatures to Kelvin, then they don’t need a degree symbol :)
Ref: http://physics.nist.gov/cuu/Units/kelvin.html
The degree sign wasn’t the issue (especially since you need the K anyway), lining up the numbers was.
Try using the ?-sign. It alligns the numbers by their decimal points:
How about right aligning the column (and indenting if required)?
I know it’s probably not the fancy number format you were after, but the Cs would line up at least…
You might want to try:
* +#°C;* -#°C;* 0°C
Note the blank after the asterisk. This lines up the Cs nicely regardless of the cell alignment. The only ‘limitation’ is that the output will always be aligned to the right.
To align symbols left and degree C right, use
_-“+”* ##0? °C”_-;_-“-“* ##0? °C”_-;_-* 0? °C”_-;_-@_-
The use of the ‘En’ rule Alt+150 is a better option than the minus sign
For aligning issues you will need the “?” like Jazzer said. Combined with the underscore trick you should get what you want.
This will line up the C’s for any cell alignment between -999 and 999°C:
_- ??0°C;- ??0°C
or if you also want a (+) sign in front of positive numbers:
+_-??0°C;-_+??0°C;_-_+??0°C
And for bonus points you can use colors:
[Red]+_-??0°C;[Blue]-_+??0°C;_-_+??0°C
Hmmm….The “?” only works in cells with general or right alignment, not including left or center as Dick asked. And while it seems to be OK in Tahoma, the help file says to use a fixed-width font:
? adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font, such as Courier New.
Meaning I’d guess that Tahoma is fixed-width for digits. I’d think Andrew has the “bestest” format.
…mrt
I would say that you really need to combine the “?” with the en-dash Alt-150 (as Jon pointed out), as
+??0°C;??0°C
(no need for a separate format for zero). That will align left, right, or center without unnecessary spaces on the left.
Hi,
I want the temperature to be displayed in decimal points like 2.95 degree Centigrade.. What would be the format for that? Please help.
Thanks!
I got it.
Thanks