Forum Discussion
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 Green. If it is between 90.0% and 94.99 % the cell is yellow. If it is below 89.99%, the column is red.
I am having a terrible time figuring this out. I can manually calculate each of the rows/columns--but there are many, and I would like the formulas to work, as the targets in Column H change from year to year.
Can anyone help?
3 Replies
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)
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?
If you always compare with $H$8 it looks like
Perhaps you may modify attach sample to make it more close to your data
