Forum Discussion
Insert Degree Symbol in Average Calculation
I have a list of temperatures that is 364 rows long and have an average calculation at the bottom. I am attempting to find a way to enter, if possible, the degree symbol ASCII 0176 in the line of temps as well as the overall average. When done in VBA/Macro using a script I copied, it changed the formatting of the filler color and number colors but did put the degree sign in. It expanded my average into an additional 9 digits to the right of the decimal point. I'm attempting to deal in whole degrees WITH the degree sign leaving my conditional formats in place alone. Any help???
Thanks,
TRS111
TRS111 Perhaps a custom format can achieve what you need. Create a custom format like in the first picture.
......and apply that format to the numbers that represent temperatures. In the example below, C2 contains the number 10.5 but it is displayed as 10.5 ℃
- Riny, I appreciate the reply. I could not get the suggestion put into practice, however. I think the problem I am running into is that there are already conditional formats in these columns and then, on top of that, the average calculation done at the bottom to show average temps is, yet, another calculation. The additions I've tried so far tend to totally wipeout the pre-existing formatting and it doesn't even allow me to utilize the character stroke ALT-0176 to put the degree symbol in. If I do a simple insert, it fouls things up as well. I guess I'll just abandon the effort but really appreciated your help. My best to you. Tom
TRS111 Not sure I follow. The custom format determines how the number is displayed. It is not influenced by conditional formatting (cf). Thus, you should be able to "attach" the Celcius sign to a number and still use cf on it.
Perhaps you can upload a portion of your workbook that has with temperatures and averaging formulae in it, as well as some conditional formats.
I agree with Riny_van_Eekelen
Number formatting is the correct way of addressing your problem since it leaves the cell value unchanged but displays it in any manner you require. It is unusual to change the number formatting within a conditional format but it is quite possible.
What you should try to avoid is inserting spurious characters into a number because calculation then becomes arduous.
- Thank you for your answer. The cinditional formatting came about to indicate temps above or below certain numbers so heat (in Texas) and cold +/- certain temps would color code the long column I shall try a rework.
The image is nothing more than number formatting. White text on a black background with smaller numbers in Cyan and larger values in red.
[Cyan][<40]0°F;[Red][>80]0°F;0°F
