GEO-Fencing Formula

usef

Active Member
Joined
Jul 31, 2007
Messages
268
Hello,

i have a spreadsheet with coordinates in column B and C, and pre defined landmarks coordinates in a table located from L1:N50 headers ("Lat", "long", "Landmark name")

what im trying to do is have a formula in column D to do something similar to a vlookup to the landmark table. This should return the name of the landmark of the location if within 100 meters of the landmark in any direction (circle).

Examples:

landmark table
L M N
24.47423167 54.32243833 Marina Mall

location sample
B C D
24.4203666 54.479036 False
24.4745575 54.323085 Marina Mall
24.4698849 54.322435 False


TIA
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Code:
      -----A----- -----B----- ---C----
  1       Lat         Lon             
  2   24.47423167 54.32243833         
  3                                   
  4       Lat         Lon     Distance
  5    24.4203666   54.479036  16,937 
  6    24.4745575   54.323085      75 
  7    24.4698849   54.322435     483

The formula in C5 and down is

=111120*SQRT( (A5-A$2)^2 + (COS(RADIANS(A5)) * (B5-B$2))^2)

The formula is approximate.
 
Upvote 0
Thank shg, the formula works fine!

but the issues here is i will have a table of 200 rows with data looking like the bellow example. each location has a unique name (identifier). and the formula should be placed in another column where it would have more than 10000 rows. each row is in the same format as bellow. A= identifier, B= x, C= y

When pasting the formula across the 10000 rows it should return a name from the 200 table if its within the 100 meeter range, if not then it should stay blank.

Thanks Again for your great help

Code:
--A----- ---B-------C--------

IP01	54.660202	24.3648453
IP02	54.6062775	24.3150902
IP03	54.5708504	24.3545246
IP04	54.5621414	24.3646889
IP05	54.4923172	24.419857
IP06	54.4914589	24.4204235
IP07	54.5003433	24.4164391
IP08	54.6123085	24.3184338
IP09	54.5696945	24.3968201
IP10	54.9912071	24.1917801
IP11	54.5247192	24.3833351
 
Upvote 0
<TABLE style="WIDTH: 217pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=290><COLGROUP><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 2450" span=2 width=54><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1865" width=41><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1499" width=33><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2706" width=59><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=16 width=49>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=54>Lat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=54>Lon</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 31pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=41></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=33></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 44pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=59></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.11827</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.33206</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.75719</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.51701</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.77213</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.91158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.33504</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.70891</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.44542</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.50773</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.34350</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.06701</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.43676</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.66317</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.85401</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.09525</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.74650</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.38283</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Loc10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.95787</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.85472</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=16 width=49>Whatever</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=54>Lat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=54>Lon</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=41>Position</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=33>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=59>Distance</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.14603</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.57717</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>24,891 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.29495</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.56981</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>14,775 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.33255</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.28032</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>21,632 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.09141</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.13805</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>19,906 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.70199</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.26015</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>13,336 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.21607</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.62507</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>15,715 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.23316</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.54208</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>20,345 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.29829</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.84069</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>13,957 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.05435</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.82725</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>33,422 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.89839</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.02866</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>8,329 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.62850</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.36828</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>13,194 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.18273</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.33794</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>7,188 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.60382</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.59853</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>18,928 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.03279</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.79741</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>34,766 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.13137</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.23023</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>10,429 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.89792</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.31096</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>18,319 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>24.67944</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 align=right>54.59156</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34>11,459 </TD></TR></TBODY></TABLE>

D14 computes the index of the closest point in the upper table with this array formula:


Code:
[COLOR=blue]=MATCH(MIN((B14 - $B$2:$B$11)^2 + (COS(RADIANS(B14)) * (C14 - $C$2:$C$11))^2), [/COLOR]
[COLOR=blue]          (B14 - $B$2:$B$11)^2 + (COS(RADIANS(B14)) * (C14 - $C$2:$C$11))^2, 0)[/COLOR]
E14 returns the name of the closest location:

=INDEX($A$2:$A$11, D14)

F14 returns the distance in meters:

=111120*SQRT( (B14 - INDEX($B$2:$B$11, D14))^2 + (COS(RADIANS(B14)) * (C14 - INDEX($C$2:$C$11, D14)))^2)
 
Last edited:
Upvote 0
There was a slightly similar question a couple of years ago, http://www.mrexcel.com/forum/showthread.php?t=403690

If you have large amounts of data (& I see mention of 10,000 rows) you may encounter a similar problem to that thread - it was 6 hours to calculate using array formulas. Based on my testing, the alternative proposed took a second or two. There is also a link to another thread where it took an hour with array formulas and ~1 second with a database type approach. (FWIW, I avoid array formulas.)
 
Upvote 0
I'd expect it to take a while, with 200 rows in one section and 10000 in the other, that's 2M non-trivial expression evaluations.

I'd be very interested to see your suggestion from the other thread implemented, Fazza.
 
Upvote 0
To create a list of landmarks within 100 metres of any location. I've assumed the same formula as posted already: I don't know anything about the formula, BTW. [Also BTW, I observed some difference in the values compared to those already posted & assume this is rounding in the calculations (in the approach I've used). The values don't show in the result: if wanted, add the formula to the SELECT clause.]

One table named "BigList" and other "Landmarks". SQL
Code:
SELECT B.ID, L.`Landmark Name`
FROM BigList B, Landmarks L
WHERE 111120*((B.Lat-L.Lat)*(B.Lat-L.Lat)+COS(B.Lat*3.14159265358979/180)*(B.Lon-L.Lon)*(B.Lon-L.Lon))^.5<100
GROUP BY B.ID, L.`Landmark Name`
 
Upvote 0
Hello everybody!
Amazing Replies! i tested shg formulas and they work great! slow but perfect results. i havent reached the 10k row but will soon. and im sure that will be a killer!

i tried using fazza code but could understand it nor work with it at all. i would appreciate it if you can provide instructions on how to use vba alternative for this.

Thanks a gain to everyone who helped out!
 
Upvote 0
The linked thread provided instructions, that was just the particular SQL above.

Steps. Save the file. Give the source tables names. Start via menu path ALT-D-D-N and follow the wizard - Excel files, OK, browse for file, etc - to the end. Choose option to edit in MS Query. 'SQL' button, edit the text to be like posted already, OK to enter that & see the dataset, 'open door' icon to exit MS Query & create query table on a worksheet.

As noted already, I found slightly different numerical results and assume this is due to rounding. Which is a little surprising, so maybe something else is the reason. Maybe the chance for rounding error can be reduced by rearranging the equation?

HTH
 
Upvote 0

Forum statistics

Threads
1,224,616
Messages
6,179,911
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top