Conditional Formatting for decimals

[Ranjit1960]

Hello Experts,

I am creating an excel sheet where user will have to put values with 4 decimal places. The value will turn red if it does not have four decimal places (i.e. more or less decimal places). I formatted the cells as number with four decimals and red font, so all input values become red (regardless of decimal places). Then, I applied the conditional formatting with formula =LEN($D6&"")-FIND(".",$D6&"")=4, and conditional formatted the font black, so all input values with four decimals turn black. Everything works fine till this stage.

I have an issue while I put value with four decimal places, but zero at fourth decimal place (e.g. 1.1110). The excel doesn't consider trailing zero at a decimal, and doesn't convert the value to black. I am sure I am missing something here.

Your support will be highly appreciated.

Thanks in advance!!

[#2]

@Ranjit1960 

If you enter 1.2340, Excel stores the value 1.234 even though it is displayed as 1.2340. So it does not "know" you intended 4 decimal places.

Similarly, if you enter 1.0000, Excel will store 1 and not 1.0000.

A workaround would be to format the cells as text, or to prefix each value that you enter with an apostrophe '. Excel will then store the value as entered.

You can use the VALUE function to convert the text values to numbers (and some functions will automatically treat the values as numbers).

[#3]

Thanks @Hans Vogelaar for the explanation!
I too believe its hard to get the desired output.