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