SOLVED

Dynamic Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-3240636%22%20slang%3D%22en-US%22%3EDynamic%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240636%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20where%20there%20are%20multiple%20rows%3A%3C%2FP%3E%3CP%3EQuestion%26nbsp%3B%20%26nbsp%3B%20OveralRate%26nbsp%3B%20%26nbsp%3BMale%26nbsp%3B%20%26nbsp%3BFemale%26nbsp%3B%20PNTS%3C%2FP%3E%3CP%3Exyz%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2044%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B45%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2055%25%26nbsp%3B%20%26nbsp%3B%2030%25%3C%2FP%3E%3CP%3Exyz2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2038%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B51%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2032%25%26nbsp%3B%20%26nbsp%3B%2031%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20use%20conditional%20formatting%20for%20each%20row%20separately%20based%20on%20its%20OveralRate.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20using%20Formatting%20based%20on%20cell%20values.%20I%20am%20using%203%20point%20scale%20where%20the%20midpoint%20is%20OveralRate.%20The%20coloring%20determines%20then%20how%20far%20Male%20Female%20and%20PNTS%20are%20from%20the%20OveralRate%20-%20for%20each%20row%20separately.%20Colors%20in%20row%20xyz%20question%20will%20be%20based%20relative%20to%2044%25%20and%20xyz2%20relative%20to%2038%25.%20Easy%20to%20do%20for%20a%20couple%20of%20rows%20manually%20but%20how%20can%20I%20quickly%20do%20it%20across%20100%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esampak%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3240636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3240887%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240887%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1321756%22%20target%3D%22_blank%22%3E%40sampak88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D(C2%26lt%3B%3D%24B2%2B3)*(C2%26gt%3B%3D%24B2-3)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20the%20rule%20for%20conditional%20formatting%20for%20Overall%20rate%20%2B%2F-%203%20in%20the%20attached%20example%20file.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D((C2%26lt%3B%24B2-3)*(C2%26gt%3B%3D%24B2-6))%2B((C2%26gt%3B%24B2%2B3)*(C2%26lt%3B%3D%24B2%2B6))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20the%20rule%20for%20conditional%20formatting%20for%20Overall%20rate%20less%20%2B%2F-%203%20and%20equal%20or%20greater%20than%20%2B%2F-6%20in%20the%20attached%20example%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3240891%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1321756%22%20target%3D%22_blank%22%3E%40sampak88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D((C2%26lt%3B%24B2-3)*(C2%26gt%3B%3D%24B2-6))%2B((C2%26gt%3B%24B2%2B3)*(C2%26lt%3B%3D%24B2%2B6))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20the%20rule%20for%20conditional%20formatting%20for%20Overall%20rate%20%2B%2F-%203%20and%20%2B%2F-6%20in%20the%20attached%20example%20file.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D(C2%26lt%3B%3D%24B2%2B3)*(C2%26gt%3B%3D%24B2-3)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20the%20rule%20for%20conditional%20formatting%20for%20Overall%20rate%20%2B%2F-%203%20in%20the%20attached%20example%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3240968%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240968%22%20slang%3D%22en-US%22%3Eif%20I%20wanted%20to%20get%20into%20the%20boundary%20between%20C2%20and%20B2%20of%200.01%20to%200.03%20and%20then%20from%200.04%20to%200.06%20I%20would%20have%20to%3CBR%20%2F%3E%3D(C2%26lt%3B%3D%24B2%2B0.01)*(C2%26gt%3B%3D%24B2%2B0.03)%3CBR%20%2F%3E%3D(C2%26lt%3B%3D%24B2%2B0.04)*(C2%26gt%3B%3D%24B2%2B0.06)%3CBR%20%2F%3Edo%20I%20understand%20it%20correctly%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3240979%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240979%22%20slang%3D%22en-US%22%3Eok%2C%20worked%20it%20out.%20thanks!%3C%2FLINGO-BODY%3E
New Contributor

I have a table where there are multiple rows:

Question    OveralRate   Male   Female  PNTS

xyz                    44%         45%      55%    30%

xyz2                  38%         51%      32%    31%

 

I want to use conditional formatting for each row separately based on its OveralRate. 

This is using Formatting based on cell values. I am using 3 point scale where the midpoint is OveralRate. The coloring determines then how far Male Female and PNTS are from the OveralRate - for each row separately. Colors in row xyz question will be based relative to 44% and xyz2 relative to 38%. Easy to do for a couple of rows manually but how can I quickly do it across 100 rows.

 

Thanks!

 

sampak

4 Replies
best response confirmed by sampak88 (New Contributor)
Solution

@sampak88 

=((C2<$B2-3)*(C2>=$B2-6))+((C2>$B2+3)*(C2<=$B2+6))

This is the rule for conditional formatting for Overall rate +/- 3 and +/-6 in the attached example file.

=(C2<=$B2+3)*(C2>=$B2-3)

This is the rule for conditional formatting for Overall rate +/- 3 in the attached example file. 

 

Is this what you are looking for?

if I wanted to get into the boundary between C2 and B2 of 0.01 to 0.03 and then from 0.04 to 0.06 I would have to
=(C2<=$B2+0.01)*(C2>=$B2+0.03)
=(C2<=$B2+0.04)*(C2>=$B2+0.06)
do I understand it correctly?
ok, worked it out. thanks!

@sampak88 

You are welcome.