Conditional formatting, the difference in values in A and B greater than 2.5
Multi tool use
Conditional formatting, the difference in values in A and B greater than 2.5
I have values in A1 and B1 , C1 and D1 and so on and they are all paired.
How do i highlight the cells if the difference between the two cells are more than 2.5? Values in A1 can be smaller than B1
2 Answers
2
Trying to work out a general formula for this, not as easy as I thought.
Assuming the data starts in column B,
=OR(AND(ISEVEN(COLUMN(B1)),ABS(C1-B1)>2.5),AND(ISODD(COLUMN(B1)),ABS(A1-B1)>2.5))
applied to all of the data.
What if the first column is column A? The second part of the formula wouldn't work (because you can't reference the column before column 1) so I ended up with the rather ugly
=OR(AND(ISODD(COLUMN(A1)),ABS(A1-B1)>2.5),AND(ISEVEN(COLUMN(A1)),ABS(INDEX(1:1,COLUMN(A1))-INDEX(1:1,MAX(COLUMN(A1)-1,1)))>2.5))
=OR(AND(ISODD(COLUMN()),ABS(A1-B1)>2.5),AND(ISEVEN(COLUMN()),ABS(XFD1-A1)>2.5))
Nice bit of arcane knowledge
– Tom Sharpe
Jun 30 at 11:43
Highlight your cells:
1) Click Home > Conditional Formatting > New Rule
2) Click on Use a formula to determine which cells to format.
3) Format values where this formula is true = ABS(A1-B1) > 2.5
4) Click Format and choose the color fill you want
5) Click okay.
Note: you need to do for each paired column and replace the Cell Reference in Step 3)
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
CF does allow referencing of "the column before column 1)" (by wrapping around). Something like
=OR(AND(ISODD(COLUMN()),ABS(A1-B1)>2.5),AND(ISEVEN(COLUMN()),ABS(XFD1-A1)>2.5))should work (I say "like" because UNTESTED).– pnuts
Jun 30 at 11:09