SOLVED

Dynamic Conditional Formatting

Copper 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 (Copper 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.

1 best response

Accepted Solutions
best response confirmed by sampak88 (Copper 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?

View solution in original post