Conditional formatting to highlight significant % differences

[PensionMan]

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

A	B	C	D	E
	Average	Group A	Group B	Group C
Yes	50%	65%	46%	46%
No	50%	35%	54%	54%

<tbody>
</tbody>

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:

A	B	C	D	E
	Average	Group A	Group B	Group C
Yes	50%	65% ↑	46%	46%
No	50%	35% ↓	54%	54%

<tbody>
</tbody>

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%↑