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]

Assuming the data is in Column A starting from cell A3

Check this and revert -

=OR(AND(A2=0,OR(A3<=A1*0.9,A3>=A1*1.1)),AND(A2<>0,OR(A3<=A2*0.9,A3>=A2*1.1)))

 

[RID]

Assuming the data is in Column A starting from cell A3

Check this and revert -

=OR(AND(A2=0,OR(A3<=A1*0.9,A3>=A1*1.1)),AND(A2<>0,OR(A3<=A2*0.9,A3>=A2*1.1)))

Apologies I should have put the column information into the table.

	Column R
Row 4	91
Row 5	80
Row 6	93
Row 7	90
Row 8	92
Row 9	97
Row 10	0
Row 11	81
Row 12	82

 

[RID]

There is no data in rows 1, 2 & 3 this is table headers. This is an actual list of readings taken over a few days

 

[SanjayGMusafir]

Slight modification to the formula needed. Use as below -

=OR(AND(R4=0,OR(R5<=R3*0.9,R5>=R3*1.1)),AND(R4<>0,OR(R5<=R4*0.9,R5>=R4*1.1)))

 

[SanjayGMusafir]

Slight modification to the formula needed. Use as below -

=OR(AND(R4=0,OR(R5<=R3*0.9,R5>=R3*1.1)),AND(R4<>0,OR(R5<=R4*0.9,R5>=R4*1.1)))

Rather try this

=OR(AND(R3=0,OR(R5<=R3*0.9,R5>=R3*1.1)),AND(R4<>0,OR(R5<=R4*0.9,R5>=R4*1.1)))

 

[RID]

Thats not worked I don't think, is the formula just basing it on the value of cell R4 only, for the purpose of the conditional formatting, say the data in R4 is data set point 1, the formula will then calculate if there is 10% change in data set point 2 R5, if there is no change out with 10% it should not change colour, the formula then moves onto the percentage change between data set point 2 R5 and data set point 3 R6, there is a 10% change here so I would like the cell to show R6 has got >10% change from R5 and it would be highlighted. The formula would then go on following this pattern of just taking the previous cell only and with the next cell. The zero has been identifed as a change but can and IF function be inserted here to ignore or am and jump to the previous cell as the new calculation, so for exampl when it comes R22 instead of taking R23 for the calculation because its got a zero in it, it would jump to the next cell with a value stated in, in this instance it would recognise R24 as the next cell discounting R23 because of the zero.

I hope that explains it a bit better and I appreciate you taking the time to respond.

 

[SanjayGMusafir]

I have well tested the formula I gave. You show me the formula you actually applied.

 

[SanjayGMusafir]

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:R100	Cell Value	=0	text	NO
R4:R100	Expression	=AND(R5<>"",R4<>0,OR(AND(R5=0,R6<>"",OR(R6<=R4*0.9,R6>=R4*1.1)),AND(R5<>0,OR(R5<=R4*0.9,R5>=R4*1.1))))	text	NO

 

[RID]

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.