Flagging trigger points in a row

[Garuda]

Hi,


I have a row of values in which I need to identify when a value changes by +/-5% compared to the first value; I also need to identify when another value changes by +/-5% compared to the previous time it changed by 5%.


For eg,


Values = 0.91 0.97 0.95 0.94 0.96 1.02 1.04 0.96


The formula should identify 0.97 (compared to initial value), then 1.02 (compared to 0.97) and then 0.96 (compared to 1.02)


Any ideas?

 

[Luke M]

First, I'd build a helper row in row 3. In A3, just put:

=1

This formula in B3:

=IF(ABS(B2-INDEX(2:2,MAX($A3:A3)))/INDEX(2:2,MAX($A3:A3))>5%,COLUMN(),A3)

Copy to the right as needed.


Then, your Conditional Format formula would be:

=ABS(B2-INDEX(2:2,MAX($A3:A3)))/INDEX(2:2,MAX($A3:A3))>5%