Feb 25 2021 10:42 PM
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
Feb 28 2021 09:52 PM
@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 ℃
Mar 05 2021 12:08 AM
Mar 05 2021 12:58 AM
@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.
Mar 05 2021 01:42 AM
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.
Mar 05 2021 02:57 AM
Mar 05 2021 08:39 AM
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