Forum Discussion

Simes888's avatar
Simes888
Copper Contributor
Feb 06, 2023

Excel conditional formatting formula not working as expected

I have a column in Excel that contains dates. The column is G, and the data starts on row 2. I have a conditional formatting rule which is supposed to change the colour of each cell in that column, i...
Formulas and Functions
sleibo's avatar
sleibo
Copper Contributor
Dec 02, 2023
I have a column to which I am trying to apply conditional formatting. The contents are from a =if formula [more specifically:(=IF(D2="H",IF(Q2<3,1,IF(AND(Q2<4,Q2>2),3,IF(AND(Q2<5,Q2>3),4,6))),(IF(D2="S",(IF(Q2<2,3,IF(AND(Q2<4,Q2>1),6,8))),"none")))]. I can not get conditional formatting to work. I have ensured that contents are formatted as general. I have clicked "Text to Columns". I note that if I simply enter the value of the if statement in an adjacent column, that column I can conditionally format normally. Please assist. Thank you.
HansVogelaar's avatar
HansVogelaar
MVP
Dec 03, 2023

sleibo 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

  • sleibo's avatar
    sleibo
    Copper Contributor
    Dec 03, 2023

    HansVogelaar 

    Thank you Hans.  Please see:

    https://www.dropbox.com/scl/fo/ebou2bzznsluxesftn5c2/h?rlkey=mg2ehx55asxqccrgggizj7q5v&dl=0

     

    Column R contains  the calculated field that I would like to conditionally format.  I have copied the values from column R to column S and applied conditional formatting to both R and S.  It works as intended in column S, but not in column R.

     

    Note that the basic spreadsheet was downloaded from our payroll provider, so columns A-O contain some weird formatting.

    PS I don't see a way to attach files to this forum directly, is that correct?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 03, 2023

      sleibo 

      Most users cannot attach files in this forum; some can but I don't know the criteria.

      Your formula switches from returning number values such as 6 or 8 in row 2 to returning text values such as "6" or "8" in row 3 and below; that's why the conditional formatting doesn't work.

      You can simplify the formula in R2 to

      =IF(D2="H",IF(Q2<3,1,IF(Q2>4,6,Q2)),IF(D2="S",IF(Q2<2,3,IF(Q2>3,8,6)),"none"))

      then fill down.

      • sleibo's avatar
        sleibo
        Copper Contributor
        Dec 03, 2023
        Thank you Hans, that worked! But why is the format of the results of my formula different in different cells, even though I formatted them all as "General"?

Resources