Temperature Number Formats
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?
Michael:
Dick -
Seems to work:
+###°C;_–###°C;_+_00°C
…Michael
2 May 2007, 10:34 amMichael:
That’s two minuses in a row in the middle.
…mrt
2 May 2007, 10:36 amDCardno:
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….
2 May 2007, 11:10 amJan:
Doesn’t get to 0° Kelvin in Dcarno’s experiments…
2 May 2007, 12:17 pmMichael:
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
2 May 2007, 12:32 pmJon Peltier:
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.
2 May 2007, 1:45 pmDoug Jenkins:
Convert the temperatures to Kelvin, then they don’t need a degree symbol
Ref: http://physics.nist.gov/cuu/Units/kelvin.html
2 May 2007, 7:14 pmJon Peltier:
The degree sign wasn’t the issue (especially since you need the K anyway), lining up the numbers was.
2 May 2007, 9:52 pmJazzer:
Try using the ?-sign. It alligns the numbers by their decimal points:
Jon B:
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...
3 May 2007, 3:01 amAl Paca:
You might want to try:
3 May 2007, 4:21 am* +#°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.
Andrew Hunt:
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
3 May 2007, 11:19 amEwald:
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
4 May 2007, 2:02 amMichael:
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.
4 May 2007, 10:45 am...mrt
Larry Robinson:
I would say that you really need to combine the "?" with the en-dash Alt-150 (as Jon pointed out), as
4 May 2007, 11:05 am+??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.