identifying the last figure in an cell and then format it.

  • Thread starter Thread starter Freddie
  • Start date Start date
F

Freddie

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
 
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
 
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
 
Back
Top