Condition Format +/- 10%

[RID]

Good Morning

I am trying to conditional format a column of data where a +/- 10% on previous cell would be formatted.

There are columns where 0 is present due to either a breakdown or non-working shift that needs to be in the column so cannot remove this.

For example there is a 10% swing between rows 1 & 2, rows 2&3 and rows 6&8 however between rows and 6 in row 7 there is a non-production day, is there a formula to detect the zero and point it to the most recent row in this case 6.

Am a bit stuck on this and having a complete mind black on just how to format based on +/- 10% of previous cell never mind the zero being thrown into the mix, so any sort of help would be appreciated.

91
80
93
90
92
97
0
81
82
79

 

[SanjayGMusafir]

Sanjay I have the same formula and I am getting the same result.

What I can see is that in your example it is high-lighting the previous Cell as the change, R9 & R10 for example in your list, the formula is showing the percentage change in R9 and would it be possible to highlight that there has been a percentage change in R10 not R9.

Check this and revert -

Book1
	R
4	90
5	91
6	80
7	83
8	84
9	86
10	70
11	73
12	81
13	82
14	86
15	80
16	0
17	83
18	81
19	76
20	79
21	81
22	82
23	0
24	67
25	66
26	64
27	65
28	66
29	68
30	68
31	64
32	68
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R4:R32	Expression	=AND(R3<>"",R4<>0,OR(AND(R3=0,OR(R2<=R4*0.9,R2>=R4*1.1)),AND(R3<>0,OR(R3<=R4*0.9,R3>=R4*1.1))))	text	NO

 

[RID]

When I copy your formula in, it is picking up the 10% change in R6, R10 & R24 which is correct, but it is also highlighting R4 for some reason. Just for information the R1 & R2 is blank space not used on the sheet, R3 is a the text header of the column table. I notice that R2 & R3 is mentioned in the formula also is this throwing up an error for me?

A new request just been landed on me also if I was to do another formula based on 5% to highlight a different colour would the formual change just be a replacement of where it R2<=R4*0.9,R2>=R4x1.1 and replace with R2<=R4*0.4,R2>=R4*0.6

 

[SanjayGMusafir]

for 5% change to

R2<=R4*0.5,R2>=R4x1.5

 

[RID]

Thanks Sanjay, do you know why the R4 is showing up as red currently, I copied your formula into mine but its highlight R4, I have nothing in cells R1 & R2 and R3 is a text header but I notice the formula references R2 & R3

 

[SanjayGMusafir]

Thanks Sanjay, do you know why the R4 is showing up as red currently, I copied your formula into mine but its highlight R4, I have nothing in cells R1 & R2 and R3 is a text header but I notice the formula references R2 & R3

Just add a Conditional Format (No Format - Stop if true) to Cell R4 only using

=And(R1="",R2="")

 

[RID]

Sorted thank you

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.