Home

Excel Assign Cell Color with Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-479952%22%20slang%3D%22en-US%22%3EExcel%20Assign%20Cell%20Color%20with%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-479952%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20staff%20have%20a%20target%20in%20Column%20H%2C%20and%20each%20month%20they%20enter%20their%20accomplishment.%20I%20would%20like%20to%20create%20a%20formula%20that%20says%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Column%20I%20is%2095%25%20of%20Column%20H%2C%20the%20cell%20is%20Green.%20If%20it%20is%20between%2090.0%25%20and%2094.99%20%25%20the%20cell%20is%20yellow.%20If%20it%20is%20below%2089.99%25%2C%20the%20column%20is%20red.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20a%20terrible%20time%20figuring%20this%20out.%20I%20can%20manually%20calculate%20each%20of%20the%20rows%2Fcolumns--but%20there%20are%20many%2C%20and%20I%20would%20like%20the%20formulas%20to%20work%2C%20as%20the%20targets%20in%20Column%20H%20change%20from%20year%20to%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-479952%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-480138%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Assign%20Cell%20Color%20with%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-480138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310109%22%20target%3D%22_blank%22%3E%40ShellyVrsek%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20apply%20conditional%20formatting%20rules%20to%20your%20I%20column%20range%20with%20formulas%20like%3C%2FP%3E%0A%3CPRE%3E%3D%24I1%26gt%3B%3D0.95*%24H1%0A%0A%3D(%24I1%26gt%3B%3D0.9*%24H1)*(%24I1%26lt%3B0.95*%24H1)%0A%0A%3D%24I1%26lt%3B0.9*%24H1%3C%2FPRE%3E%0A%3CP%3Eaccordingly%20to%20each%20rule%20and%20assuming%20your%20range%20starts%20from%20first%20row%20(or%20adjust%20otherwise)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-480604%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Assign%20Cell%20Color%20with%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-480604%22%20slang%3D%22en-US%22%3E%3CP%3ET%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20Thanks%20so%20much%20for%20your%20response.%20It%20seems%20like%20that%20should%20work%2C%20but%20it%20is%20not%20working%20properly.%20I'm%20not%20sure%20what%20I%20am%20doing%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20changed%20it%20to%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DI8%26gt%3B%3D.95*%24H%248%3C%2FP%3E%3CP%3EAs%2C%20H8%20will%20consistently%20be%20the%20target.%20It%20is%20not%20affecting%20the%20color%2C%20or%20always%20turning%20it%20red--even%20if%20the%20value%20is%20less.%20Some%20of%20my%20values%20are%20formatted%20as%20percentages--meaning%20the%20H8%20is%20entered%20as%20a%20percentage%2C%20and%20so%20it%20so%20is%20the%20data%20entered%20in%20i7.%20Could%20that%20be%20the%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-480635%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Assign%20Cell%20Color%20with%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-480635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310109%22%20target%3D%22_blank%22%3E%40ShellyVrsek%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20always%20compare%20with%20%24H%248%20it%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20529px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109832iAF14A1E83DD0D29A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20modify%20attach%20sample%20to%20make%20it%20more%20close%20to%20your%20data%3C%2FP%3E%3C%2FLINGO-BODY%3E
ShellyVrsek
New Contributor

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)

 

T@Sergei Baklan 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?

 

 

 

@ShellyVrsek ,

 

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

image.png

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies