Conditional Formatting Based on +/- % of Average

[thbutterflycollector_]

Hi,

I am trying to apply an formula to highlight prices in Columns G, L and Q that are outside +/- a stated percentage of the average value for that price:

	Column G	Column L	Column Q	Column T
Row 1				10%
Row 2	A	B	C	Median/Average
Row 3 - Nuts	31​	42​	32​	35
Row 4 - Bolts	11​	9​	7​	9

The % in T1 should be such that it can be manually changed and the conditional formatting updates accordingly. Any solutions appreciated.

 

[SanjayGMusafir]

Check this and revert -

Use following formula to CF Column G, L & Q

=$T1<>$T$1

 

[Peter_SSs]

See if this does what you want.

24 01 11.xlsm
	G					L					Q			T
1														10%
2	A					B					C			Median/Average
3	31					42					32			35
4	11					9					7			9
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q3:Q4,L3:L4,G3:G4	Expression	=ABS(G3-$T3)/$T3>$T$1	text	NO

 

[thbutterflycollector_]

See if this does what you want.

24 01 11.xlsm
	G					L					Q			T
1														10%
2	A					B					C			Median/Average
3	31					42					32			35
4	11					9					7			9
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q3:Q4,L3:L4,G3:G4	Expression	=ABS(G3-$T3)/$T3>$T$1	text	NO

Thank you - perfect! 8)

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.