Forum Discussion

ShellyVrsek's avatar
ShellyVrsek
Copper Contributor
Apr 22, 2019

Excel Assign Cell Color with Formula help

I have a spreadsheet where staff have a target in Column H, and each month they enter their accomplishment. I would like to create a formula that says:   If Column I is 95% of Column H, the cell is...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 22, 2019

ShellyVrsek , you may apply conditional formatting rules to your I column range with formulas like

=$I1>=0.95*$H1

=($I1>=0.9*$H1)*($I1<0.95*$H1)

=$I1<0.9*$H1

accordingly to each rule and assuming your range starts from first row (or adjust otherwise)

 

  • ShellyVrsek's avatar
    ShellyVrsek
    Copper Contributor
    Apr 22, 2019

    TSergeiBaklan Thanks so much for your response. It seems like that should work, but it is not working properly. I'm not sure what I am doing wrong.

     

    I have changed it to this formula:

    =I8>=.95*$H$8

    As, H8 will consistently be the target. It is not affecting the color, or always turning it red--even if the value is less. Some of my values are formatted as percentages--meaning the H8 is entered as a percentage, and so it so is the data entered in i7. Could that be the issue?

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 22, 2019

      ShellyVrsek ,

       

      If you always compare with $H$8 it looks like

      Perhaps you may modify attach sample to make it more close to your data

Resources