Conditional formatting to highlight significant % differences

[PensionMan]

Hi All,
Thanks for opening this up.
I have an export of survey findings like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Average[/TD]
[TD]Group A[/TD]
[TD]Group B[/TD]
[TD]Group C[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]50%[/TD]
[TD]65%[/TD]
[TD]46%[/TD]
[TD]46%[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]50%[/TD]
[TD]35%[/TD]
[TD]54%[/TD]
[TD]54%[/TD]
[/TR]
</tbody>[/TABLE]

I would like to format this in a way that columns C-E are highlighting significant differences of more than 5% compared to the average so that it looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Average[/TD]
[TD]Group A[/TD]
[TD]Group B[/TD]
[TD]Group C[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]50%[/TD]
[TD]65% ↑[/TD]
[TD]46%[/TD]
[TD]46%[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]50%[/TD]
[TD]35% ↓[/TD]
[TD]54%[/TD]
[TD]54%[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone point me in the right direction please?

Thank you,

PensionMan

 

[tyija1995]

Hey,

I'd set up 2 Conditional Formatting (CF) rules as follows:

Assuming you have your averages in cells B2 & B3 with the Group A - C data in C2:E3 then,

Highlight C2:E3 -> CF -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:

=C2>=$B2+0.05 -> Format as you want it -> OK -> OK

2nd rule:

=C2<=$B2-0.05 -> Format as you want it -> OK -> OK

 

[PensionMan]

That's great, thank you!
Only thing I don't seem to be able to do is add those up / down arrows. Is that possible to add at all?
Thank you again!

 

[tyija1995]

Hey,

If you go into CF -> Manage Rules -> Select the first rule (-5% rule lets say) -> Edit Rule -> Format -> Number Tab -> Custom Category -> 0%↓ -> OK
Then do the same process for the +5% rule 0%↑