Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formal not working
Here's the deal...I'm importing a file from an email. I'm trying to CF the
cells to light up red when the value is greater than 5. When I use that condition, ALL cells turn red. If I delete the numbers that are in the cells and type them in myself, the CF works properly. Why doesn't the formatting work with the numbers I've previously pasted in? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formal not working
It sounds as if the values you've copied in from the email are text, and not
numbers. Any text is counted as being greater than any number. You can check whether it's text or number by =ISTEXT(A2) and =ISNUMBER(A2). If they are text, you might be able to convert, either by Data/ Text to Columns, or by Edit/Paste Special to add zero or multiply by 1. -- David Biddulph "mailrail" wrote in message ... Here's the deal...I'm importing a file from an email. I'm trying to CF the cells to light up red when the value is greater than 5. When I use that condition, ALL cells turn red. If I delete the numbers that are in the cells and type them in myself, the CF works properly. Why doesn't the formatting work with the numbers I've previously pasted in? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formal not working
Most likely they have not come in as numbers... they may have a space or
other characters... If they have come in as text then enter 1 anywhere in the sheet, copy it, select your column with numbers, right-click and choose Paste Special|Multiply... to convert them to numbers... If this does not work then try TRIM to remove extra characters then do the above. "mailrail" wrote: Here's the deal...I'm importing a file from an email. I'm trying to CF the cells to light up red when the value is greater than 5. When I use that condition, ALL cells turn red. If I delete the numbers that are in the cells and type them in myself, the CF works properly. Why doesn't the formatting work with the numbers I've previously pasted in? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formal not working
You got 2 very good advices already, but here my little chip-in: set your CF
as =value(yourCell)5... This will transform whatever you pasted in into a value during the process of evaluating whether or not it is greater than 5, without actually changing the content of your cell. "mailrail" wrote: Here's the deal...I'm importing a file from an email. I'm trying to CF the cells to light up red when the value is greater than 5. When I use that condition, ALL cells turn red. If I delete the numbers that are in the cells and type them in myself, the CF works properly. Why doesn't the formatting work with the numbers I've previously pasted in? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formal not working
Hi,
Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. -- Thanks, Shane Devenshire "mailrail" wrote: Here's the deal...I'm importing a file from an email. I'm trying to CF the cells to light up red when the value is greater than 5. When I use that condition, ALL cells turn red. If I delete the numbers that are in the cells and type them in myself, the CF works properly. Why doesn't the formatting work with the numbers I've previously pasted in? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting ROW not working | Excel Discussion (Misc queries) | |||
Need Help on formal - output a sum | Excel Worksheet Functions | |||
Reset Text Direction in Formal Cells Menu | Excel Discussion (Misc queries) | |||
Conditional Format Not Working | Excel Worksheet Functions | |||
Conditional Formatting is not working... | Excel Worksheet Functions |