Conditional Formatting based on Increase/Decrease of percentage

[goku0650]

Hello, I am looking to highlight any cells Green that have at equal or greater of 5% increase from previous month. Then I am looking to highlight any cells Red that have a decrease of 5% of previous month. Below is a sample of using the Mini Sheet option as well as a screenshot. The Column "Charges" starts on A4. Thanks!

FY 2022 - Bill Wright.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	Charges	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Total
5	411	2.50	-	1.50	0.50	2.00	2.50	-	1.50	0.50	1.50	-	-	-	-	-	-	-	-	12.50
6	411 LIST	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
7	ACT-800TFN	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
8	ACT-DID	4.00	12.00	21.00	5.50	2.50	8.50	8.75	12.25	8.00	20.25	-	-	-	-	-	-	-	-	102.75
9	ACT-DID800	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
10	ACT-DIDINTL	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
11	CHARGEBACK FEE	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
12	CNAM	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
13	CNAM - PER CALL	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
14	CNAM-PER TN	624.29	631.86	640.50	633.89	617.30	490.08	494.98	485.35	486.43	485.23	-	-	-	-	-	-	-	-	5,589.91
15	CPAAS TERM	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Summary By Customer

Cell Formulas
Range	Formula
B5:S15	B5	=SUMIFS(Summary!G:G,Summary!$C:$C,'Summary By Customer'!$A$2,Summary!$E:$E,'Summary By Customer'!$A$5:$A$119)
T5:T15	T5	=SUM(Table5[@[Jan-21]:[Jun-22]])

Named Ranges
Name	Refers To	Cells
_1_POINT_COMMUNICATIONS_9512	='Summary By Customer'!$A$2	B5:S15
Customers	='Summary By Customer'!$A$2	B5:S15

 

[goku0650]

On another note, these amounts are calculated based on a name from a drop down list. So these cells will change based on the customer's name.

 

[Peter_SSs]

Welcome to the MrExcel board!

It is not clear just how you want to treat the cell if it, or the previous cell, is zero. However, see if this gets you started.

goku0650.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	Charges	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Total
5	411	2.5	0	1.5	0.5	2	2.5	0	1.5	0.5	1.5	0	0	0	0	0	0	0	0	12.5
6	411 LIST	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	ACT-800TFN	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	ACT-DID	4	12	21	5.5	2.5	8.5	8.75	12.25	8	20.25	0	0	0	0	0	0	0	0	102.75
9	ACT-DID800	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	ACT-DIDINTL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	CHARGEBACK FEE	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	CNAM	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	CNAM - PER CALL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	CNAM-PER TN	624.29	631.86	640.5	633.89	617.3	490.08	494.98	485.35	486.43	485.23	0	0	0	0	0	0	0	0	5,589.91
15	CPAAS TERM	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:S15	Expression	=AND(B5<>0,C5<=0.95*B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5>=1.05*B5)	text	NO

 

[goku0650]

Thank you very much that seemed to do the trick.

 

[Peter_SSs]

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

 

[goku0650]

@Peter_SSs, I was wondering if you can help me again with the above. So instead of just having Red/Green, I was asked to provide the following colors based on percentage ranges. Thanks!

Revenue changes	Colors
no changes	Blue
increase 1-9%	Yellow
increase 10% - 15%	Light Green
increase 15%+	Green
decrease 1-9%	Pink
decrease 10% - 15%	Red
decrease 15%+	Orange

 

[Peter_SSs]

Like this?

goku0650.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	Charges	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Total
5	411	2.5	0	1.5	0.5	2	2.21	0	1.5	0.5	1.5	0	0	0	0	0	0	0	0	12.5
6	411 LIST	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	ACT-800TFN	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	ACT-DID	4	12	21	5.5	2.5	8.5	8.75	12.25	8	20.25	0	0	0	0	0	0	0	0	102.75
9	ACT-DID800	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	ACT-DIDINTL	0	0	0	5	5	5	4.4	0	0	0	0	0	0	0	0	0	0	0	0
11	CHARGEBACK FEE	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	CNAM	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	CNAM - PER CALL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	CNAM-PER TN	624.29	631.86	640.5	633.89	617.3	490.08	494.98	485.35	486.43	485.23	0	0	0	0	0	0	0	0	5,589.91
15	CPAAS TERM	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:S15	Expression	=AND(C5<>0,C5=B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5>B5,C5<1.1*B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5>=1.1*B5,C5<1.15*B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5>=1.15*B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5<B5,C5>0.9*B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5<=0.9*B5,C5>0.85*B5)	text	NO
C5:S15	Expression	=AND(C5<>0,C5<=0.85*B5)	text	NO

 

[goku0650]

Hello @Peter_SSs, yes thank you so much as I wasn't sure about the in between percentages. I greatly appreciate your help with this.

 

[Peter_SSs]

You're welcome.