Conditional formatting of entire column [Solved/Closed]

tim - Nov 30, 2010 at 12:24 PM - Latest reply:  SomeGuy
- May 1, 2014 at 11:02 AM
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
See more 

4 replies

TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Dec 2, 2010 at 09:46 AM
-1
Thank you
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 53263 Posts Monday February 1, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Dec 2, 2010 at 04:32 PM
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.