Conditional formatting of entire column [Solved/Closed]

Report
-
 SomeGuy -
Hello,

I have two columns in excel 2010 which are generated by sensors measuring the same thing. In theory the 2 columns should be the same...of course they are not ;).

So...I would like to highlight any cell in column 1 which is more than 2% different than the cell in the same row of column 2. This indicates a measurement error.

I tried something like -> {cell value < ="($Q:$Q)" applies to =$H:$H }which works for direct comparison but if I change it to ="($Q:$Q)*0.98" or ="($Q:$Q)"*0.98 it doesn't work. Is there a way to do column math in this situation.

I know I could make another column and do the math there and then format that but I already have LOTS of columns so I would rather not. This is also only one of many other conditions imposed on the data so it is not just adding one extra column it would be many more.

I am trying to use conditional formatting as a filtering technique, the reason being that I need to filter in time not just values. Meaning I need a certain temporal stability in the reading before the datapoint is valid so if I simply begin filtering data then I may loose the temporal data from adjacent rows, I need 5 minutes worth and the data is taken at 30s intervals. I plan to conditionally format the entire dataset and then filter by format.

an alternate suggestion on how to filter this would be fine or if you know how to do math on column comparisons that would be great.

thanks

1 reply

Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Tim,

Quote
So...I would like to highlight any cell in column 1 which is more than 2% different than the cell in the same row of column 2. This indicates a measurement error.
Unquote

When you are trying to do this, don't compare it to the whole column, but look at cell level.
Let me start by giving a example to see if we are on the same page.

If H1 is 1 and Q1 is 1.02 then it is OK.
If H1 is 1 and Q1 is 0.98 then it is OK.
If H1 is 1 and Q1 is 1.03 or bigger then format change of H1.
If H1 is 1 and Q1 is 0.98 or smaller then format change of H1.

If this is OK use the following:
Select H1 and goto conditonal format.
Select formula is:
=OR(Q1>H1*1.02,Q1<H1*0.98)

Now drag H1 down and select from the small square "format only".

Best regards,
Trowa
Ambucias
Posts
51403
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
April 4, 2020
14,511
Yes TrowaD! Thank you! Impressive!
Thanks TrowaD!
Using the fill handle does not work because all the cells refer back to Q1. The reference is absolute not relative.