Help comparing column values

[NotASuperGenius]

I am working on a spreadsheet that includes the location of ~80k global cities as defined by an internal system (Gov), Bing and Google. I would like the audit the data to identify entries where the internal system may be incorrect or where there is significant inconsistency between sources. Since the values are rarely identical, I added columns for the straight line distances between points to analyze. I would like to add a column (manually added in the example below) that describes how the sources compare to each other

- Are they all within 1Km of each other = Alignment
- Are they all in conflict (greater than 1KM) = Conflict
- Is Gov inconsistent with Bing & Google = Gov Variance
- Is Bing inconsistent with Gov and Google = Bing Variance
- Is Google inconsistent with Gov and Bing = Google Variance

* Note, not trying to determine which value(s) is/are correct. I am looking for a general idea of how location definition varies.

Sample data below. As always, thanks for the assist - NASG

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Location	Gov		Bing		Google		Gov vs. Bing	Gov vs. Google	Bing vs. Google	Alignment		Alignement Values
2	Query	Lat	Long	Lat	Long	Lat	Long	(km)	(km)	(km)
3	New York, NY, US	40.75348889	-73.98096111	40.7820015	-73.83170319	40.7127837	-74.0059413	8.06	3.1	10.3	Conflict		Aligned
4	Corpus Christi, TX, US	27.79628889	-97.40355	27.79640961	-97.40355682	27.8005828	-97.396381	0.01	0.53	0.53	Aligned		Conflict
5	ATL, GA, US	33.640772	-84.447868	33.74831009	-84.39111328	33.7489954	-84.3879824	8.12	8.23	0.19	Gov Variance		Gov Variance
6	Macon, GA, US	32.83608056	-83.66258056	32.83967972	-83.62757874	32.8406946	-83.6324022	2.05	1.78	0.29	Gov Variance		Bing Variance
7	Puerto Vallarta, , MX	20.61448889	-105.2393111	20.60713959	-105.2243729	20.653407	-105.2253316	1.09	2.84	3.2	Conflict		Google Variance
8	Plano, TX, US	33.04641111	-96.74486944	33.0207901	-96.69924927	33.0198431	-96.6988856	3.18	3.24	0.07	Gov Variance
9	Halifax, NS, CA	44.6488	-63.57536111	44.64960098	-63.59469986	44.6488625	-63.5753196	0.95	0.01	0.95	Aligned
10	EWR, NJ, US	40.69069	-74.17741	40.69287872	-74.18544769	40.6895314	-74.1744624	0.45	0.17	0.62	Aligned
11	Milan, IT	45.46421944	9.189780556	45.45199966	9.192770004	45.4654219	9.1859243	0.86	0.21	0.99	Aligned
12	New Orleans, LA, US	29.95368889	-90.07776111	29.95369911	-90.07775116	29.9510658	-90.0715323	0	0.42	0.41	Aligned
13
Sheet1

 

[cruisen]

Dear NASG,

for my approach to get this done, there are some steps needed:


1.) Geometric cases:

for a complete picture, i suggest to consider to add 3 more cases. Even if rare, there is the possibility, that on coordinate is more or less in the middle of the two others. I call them "centred".


2.) Lookup table


On an other sheet (#2) create a lookup table like this:

[TABLE="width: 178"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Byte[/TD]
[TD]Alignment[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Aligned[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GOV centred[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BING centred[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GOOGLE variance[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GOOGLE centred[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BING vary[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]GOV variance[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Conflict[/TD]
[/TR]
</tbody>[/TABLE]

And define a name to the table like "LUT" (for look up table)



3.) Criteria maximum distance


to make things more flexible, please also find space on sheet #2 to define your maximum allowed distance, in your case you mentioned 1 for 1 KM, and also define the name for it: "MD", for max distance.




4.) assigning alignment text

- first clear column M in your table


- enter in L3: =IF(H3>MD,1,0)

which tests if the distance is higher or lower your maximum distance, and if it is lower, outputs a "0"


- auto fill column L, M and N, down to the last row of your table



- enter in O3: =L3*2^2+M3*2+N3

which will calulate a number from 0 to 7 (decimal) from these 3 "bits"


- auto fill column O down to the last row of your table


- enter in K3: =VLOOKUP(O3,LUT,2,FALSE)

which will lookup the text from the look up table


- auto fill column K down to the last row of your table



5.) Remarks

I hope I did understand your problem correctly.

for further statistical analysis, the values in column O are easier to work with.


Regrads, NvK

 

[NotASuperGenius]

Huzzah! That was awesome. Thank you!

 

[cruisen]

Huzzah! That was awesome. Thank you!

You are very welcome.

btw: i am Interested in how you calculate the distance between 2 coordinates, since as far as I know one should use great circle approximation and that the distance are getting shorter when you are moving from equator to one of the poles ...

 

[NotASuperGenius]

I used a custom function from

Calculate distance in Google spreadsheet | Winfred van Kuijk

The biggest challenge was working around the API limits.