Forum Discussion

Ranjit1960's avatar
Ranjit1960
Copper Contributor
Aug 04, 2022

Conditional formatting for decimals

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 decimals places). I formatted the cells as number with four decimals and red font, so all input values become red (regardless of decimals 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 decimals 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!!

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    You'll probably have better response if you post your Excel question in an Excel-specific forum, rather than this one, which supports Access.
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    you can use this condition, where A1 is the start of the range to format:

    =AND(ISERROR(LEN(A1)-FIND(".",A1))=FALSE, LEN(A1)-FIND(".",A1)<>4)
    • Ranjit1960's avatar
      Ranjit1960
      Copper Contributor
      arnel_gp
      Thanks for the formula! This is still not giving me the required result. Now I started getting the values with all the decimals turning black.
      I want just the four decimals values turning black, even if the fourth decimal is zero.
      • arnel_gp's avatar
        arnel_gp
        Steel Contributor
        i don't think it would be possible with Trailing 0, since Excel automatically remove them if the the cell is not formatted as Text.

Resources