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