Flagging cells with values are a certain percentage off from values in surrounding cells

[Bigred25]

if I have a row of values:

example:

22.9 7.0 24.0 27.1 8.0 24.0 13.0 17.0 5.0 37.0 9.0 19.0 42.0


What would be the best way to "flag" any of the values that are, say, >=20% different than the value in the cell to the left or right?

Thanks in advance for any help! It is greatly appreciated!

[cffndncr]

Say your values are in row 1, you could use the following formula in conditional formatting:

=IF(OR(B1+(B1*$B$4)<A1,(B1*$B$4)+B1<C1,B1-(B1*$B$4)>A1,B1-(B1*$B$4)>C1),TRUE), where $B$4 is the percentage difference you want to use.

I have attached a sheet showing how that formula returns TRUE/FALSE for the conditional formatting. It won't work on your first or last values though; I can look at a workaround that will include the first and last values as well if you need it.

[FDibbins]

Maybe this, using cff's workbook...
=OR(B1/A1>=1+$B$4,B1/C1>=1+$B$4)

Note, you dont need the IF() component, you can just use...
=OR(B1+(B1*$B$4)<A1,(B1*$B$4)+B1<C1,B1-(B1*$B$4)>A1,B1-(B1*$B$4)>C1)
CF works on TRUE or FALSE, so that condition is either TRUE, (rule triggers) or it's not - FALSE or ERROR (rule does not trigger)

[cffndncr]

Very clever!

Slight amendment to your formula:

=OR(B1/A1>=1+$B$4,B1<A1<=1-$B$4,B1/C1>=1+$B$4,B1<C1<=1-$B$4)

[FDibbins]

Thanks for the feedback (in a freaky good way lol)

I saw you included a test both ways, but I read the OP request of ">=20% different than" as the cell being greater than or = to the cells either side of it...

6...10...6
10 is more than 20% greater than either side

9...10...13
10 is not greater than either cell

hmmm now here's a question. Assuming I have understood correctly, 10 would get highlighted in the 1st example, but not the 2nd. But what about this 1?
9...10...6
10 is not greater than the left cell, but is greater than the right cell

[cffndncr]

I assumed he meant the difference had to be >=20%, regardless of which direction that difference was.

OP?

[Bigred25]

Apologies. Need to better clarify my problem.

If I have the following set of #'s, how can I flag a value in a cell if it is more than 20% different than the value in the cell only to the left of it (i.e., if B1 >= or 20% (+ or -)change from the value in A1, if C1 >= 20% (+ or -) change from the value in B1, etc)
Example:

22.9 7.0 24.0 27.1 8.0 24.0 13.0 17.0 5.0 37.0 9.0 19.0 42.0


In the set of numbers above, I would expect 7.0, 24.0, 8.0, 24.0, etc to be highlighted.

It sounds like condition formatting is exactly what i need

Thanks!!!

[FDibbins]

Did you try the suggestion in post # 4?
=OR(B1/A1>=1+$B$4,B1<A1<=1-$B$4,B1/C1>=1+$B$4,B1<C1<=1-$B$4)

[cffndncr]

If it's only in the cell to the left, it makes the formula much simpler:

=OR(B1*1.2>A1,B1*0.8<A1)

Alternatively, you can use a cell reference to specify the margin of difference you want (say you want to change from 20% to 25%).

In that case, assuming that the margin of difference you want is in cell B4, as in my previous workbook example, use this formula:

=OR(B1*(1+$B$4)>A1,B1*(1-$B$4)<A1)

Use that formula for the conditional formatting of cell B1, then use the format painter to paint it across the whole row.

[cffndncr]

Whoops! I got my signs around the wrong way. I've had a coffee now, let's try this again.

=OR(B1*(1+$B$4)<A1,B1*(1-$B$4)>A1)

[FDibbins]

This thread is marked solved, but it seems that it is not yet complete?