Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
identifying the last figure in an cell and then format it.
Hi I want to identify if the second decimal in a cell is for example either 0
or 5 and if they are not I want the cell to be red Also, there must be only total two deciamls in the cell. /Freddie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
identifying the last figure in an cell and then format it.
A couple of questions... are you rounding to the second decimal or truncating
at the second decimal? Secondly do you need the number to only have to decimals or to only show 2 decimals? Depending on that the formulas you will need will change. If you only need to show 2 decimals then you can just format the number. Otherwise you will need to use formula in a seperate cell to round or trncate teh number. To get the red format do the following (assuming the cell you want to format is in cell A2) Select Format - Conditonal Formatting | Formula is Add this formula =NOT(MOD(TRUNC(A2*100), 5)) Select the Red Format Change A2 as nece -- HTH... Jim Thomlinson "Freddie" wrote: Hi I want to identify if the second decimal in a cell is for example either 0 or 5 and if they are not I want the cell to be red Also, there must be only total two deciamls in the cell. /Freddie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
identifying the last figure in an cell and then format it.
On Thu, 27 Aug 2009 06:56:03 -0700, Freddie
wrote: Hi I want to identify if the second decimal in a cell is for example either 0 or 5 and if they are not I want the cell to be red Conditional Formatting: Also, there must be only total two deciamls in the cell. Data/Validation Allow: Custom Formula: =INT(G1*100)=G1*100 (substitute the active cell for G1) Use a Formula: =MOD(MID(TEXT(G1,"0.00"),FIND(".",TEXT(G1,"0.00")) +2,1),5)<0 (again, substitute the active cell for G1) Format Interior as red; consider formatting font to white. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a cell format to figure out a data? | Excel Worksheet Functions | |||
Conditional Format - Identifying Highlighted Cells | Excel Discussion (Misc queries) | |||
How do I format a figure as cent (e.g. 0.015 displayed as 1.15) | Excel Discussion (Misc queries) | |||
format a six figure number to a time. | Excel Discussion (Misc queries) | |||
HOW DO I EXCLUDED A FIGURE FROM A CONDITIONAL FORMAT???? | Excel Worksheet Functions |