detecting inconsistent data

**rossryan** · 11-27-2009, 04:18 AM

Is there a way in excel that will automatically detect or put a note in a new column – “inconsistent”.

The scenario is: I am relating an existing table from another data that have been validated. Our links is the id we have created; now for example in my field column “diameter” my data is year 50 and 150 while on the validated data the data is 75. I have several columns to compare – diameter, material status decom by, and year decom. I hope there a way in excel that would put a remarks in a new cell telling me “inconsistent”. I hope you get what I mean anyway I have attached a sample.

Thanks.

**teylyn** · 11-27-2009, 05:58 AM

Hi,

If you specify an expected value in R1 and a tolerance percentage in R2, you could use a formula like this in O2 and copy down

=IF(C2/$R$1<1-$R$2,"inconsistent","")

then highlight the whole table and create a conditional format with Formula Is

=$C2/$R$1<1-$R$2

set a fill color for the row and hit OK

this way, the whole row will light up when the value in column C is xx percent off the expected value.

Alternative
If you just want to compare with the value in column J, use this in O2 and copy down

=IF(C2<>J2,"inconsistent","")

and use this for the formula in conditional formatting:

=$C2<>$J2

hth