Finding duplicates using a margin of error

Copper Contributor

Hi everyone!

 

I am trying to add a field validation to my excel spreadsheet that allows me not only to identify duplicate values that are identical (which I know how to do) but that will signal fields that are in the same range of values,

 

for example : I have a variable that is the average of two forearm length measures, and I would like to signal values that are within +/- 1 cm of each other to account for measuring errors. 

 

Does anyone know how I can achieve this ?

 

Thanks! Morgana

4 Replies

@morgana9 Could you use a conditional formatting formula?

This formula would flag anything with a variance of 1 to a specified variable.

 

I'm thinking something like:

=AND(D1 <= $G$1+1,D1>=$G$1-1)

[where the variable is in G1, and the range of cells to validate against it is in column D]

@macrordinary I'm not sure if that's exactly what I'm trying to achieve as it's not a specified value that I'd like to validate the range of cells against, but each other (signaling duplicates but with a little variability allowed)

 

but thanks anyways!

@macrordinary little update, I did end up using a little variation of your formula and making it work for my project needs. so thank you very very much!

@morgana9 Glad I could help!