conditional formatting

[aarti_rto]

Hello Experts

I Have below Data , i want to give a condition for Jan-23 to May-23

condition Jan-23 - if Jan-23 is greater than Average of Oct-22 to Dec-22 then it's upper direction if not then lower direction

for Feb-23 - if Feb-23 is greater than Average of Nov-22 to Jan-23 then it's upper direction if not then lower direction

and so on till May-23

Part Number

Apr-22

May-22

Jun-22

Jul-22

Aug-22

Sep-22

Oct-22

Nov-22

Dec-22

Jan-23

Feb-23

Mar-23

Apr-23

May-23

A

0.06​

0.07​

0.08​

0.09​

0.03​

0.11​

0.15​

0.09​

0.06​

0.11​

0.07​

0.09​

0.04​

0.11​

B

0.03​

0.03​

0.04​

0.02​

0.02​

0.02​

0.01​

0.02​

0.03​

0.01​

0.02​

0.01​

0.02​

0.01​

C

0.01​

0.00​

0.00​

0.01​

0.00​

0.01​

0.01​

0.01​

0.00​

0.01​

0.01​

0.01​

0.01​

0.00​

D

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

E

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

F

0.00​

0.00​

0.00​

0.00​

0.01​

0.01​

0.00​

0.00​

0.00​

0.01​

0.00​

0.01​

0.00​

0.00​

G

1.23​

1.28​

1.52​

1.64​

1.26​

1.06​

1.44​

1.24​

1.36​

1.10​

1.48​

1.41​

1.04​

1.77​

H

0.03​

0.09​

0.04​

0.04​

0.03​

0.06​

0.04​

0.02​

0.02​

0.01​

0.02​

0.00​

0.03​

0.02​

I

0.12​

0.09​

0.22​

0.19​

0.15​

0.15​

0.07​

0.10​

0.11​

0.16​

0.08​

0.11​

0.08​

0.15​

J

0.04​

0.07​

0.08​

0.05​

0.08​

0.06​

0.06​

0.07​

0.04​

0.06​

0.07​

0.10​

0.13​

0.13​

 

[kvsrinivasamurthy]

Select K2:O11. Formula for CF

=K2>AVERAGE(H2:J2)
format for fill--> Green

=K2<AVERAGE(H2:J2)
format for fill--> Red

 

[Peter_SSs]

Do you want something different if the value is exactly equal to the average of the previous 3 months?

23 05 30.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23
2	0.06	0.07	0.08	0.09	0.03	0.11	0.15	0.09	0.06	0.11	0.07	0.09	0.04	0.11
3	0.03	0.03	0.04	0.02	0.02	0.02	0.01	0.02	0.03	0.01	0.02	0.01	0.02	0.01
4	0.01	0.00	0.00	0.01	0.00	0.01	0.01	0.01	0.00	0.01	0.01	0.01	0.01	0.00
5	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
7	0.00	0.00	0.00	0.00	0.01	0.01	0.00	0.00	0.00	0.01	0.00	0.01	0.00	0.00
8	1.23	1.28	1.52	1.64	1.26	1.06	1.44	1.24	1.36	1.10	1.48	1.41	1.04	1.77
9	0.03	0.09	0.04	0.04	0.03	0.06	0.04	0.02	0.02	0.01	0.02	0.00	0.03	0.02
10	0.12	0.09	0.22	0.19	0.15	0.15	0.07	0.10	0.11	0.16	0.08	0.11	0.08	0.15
11	0.04	0.07	0.08	0.05	0.08	0.06	0.06	0.07	0.04	0.06	0.07	0.10	0.13	0.13
Compare to average

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:O11	Expression	=E2<AVERAGE(B2:D2)	text	NO
E2:O11	Expression	=E2=AVERAGE(B2:D2)	text	NO
E2:O11	Expression	=E2>AVERAGE(B2:D2)	text	NO

 

[aarti_rto]

Thank you , can we add upper & lower symbols for same situation?

Do you want something different if the value is exactly equal to the average of the previous 3 months?

23 05 30.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23
2	0.06	0.07	0.08	0.09	0.03	0.11	0.15	0.09	0.06	0.11	0.07	0.09	0.04	0.11
3	0.03	0.03	0.04	0.02	0.02	0.02	0.01	0.02	0.03	0.01	0.02	0.01	0.02	0.01
4	0.01	0.00	0.00	0.01	0.00	0.01	0.01	0.01	0.00	0.01	0.01	0.01	0.01	0.00
5	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
7	0.00	0.00	0.00	0.00	0.01	0.01	0.00	0.00	0.00	0.01	0.00	0.01	0.00	0.00
8	1.23	1.28	1.52	1.64	1.26	1.06	1.44	1.24	1.36	1.10	1.48	1.41	1.04	1.77
9	0.03	0.09	0.04	0.04	0.03	0.06	0.04	0.02	0.02	0.01	0.02	0.00	0.03	0.02
10	0.12	0.09	0.22	0.19	0.15	0.15	0.07	0.10	0.11	0.16	0.08	0.11	0.08	0.15
11	0.04	0.07	0.08	0.05	0.08	0.06	0.06	0.07	0.04	0.06	0.07	0.10	0.13	0.13
Compare to average

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:O11	Expression	=E2<AVERAGE(B2:D2)	text	NO
E2:O11	Expression	=E2=AVERAGE(B2:D2)	text	NO
E2:O11	Expression	=E2>AVERAGE(B2:D2)	text	NO

 

[Peter_SSs]

can we add upper & lower symbols for same situation?

This is not a strong point for me but I'm not aware if you can.