Hello Experts, I need some help in getting the distance
1. between below mentioned Lat and Long,
2. then find the value which has greater than 70% and return output which is within 3 miles and have value less than 60% with respect to the first column.
eg., check the value in Red(92.2), and it will jump to the value in Green(8.66) and get the result as 03RW316-1 since the value is below 60% and within 3 miles of radius.( just an example it moght not be exact within 3 miles.)
Site | Latitude | Longitude | 1900 | Expected result |
03RW201-1 | 38.70041666 | -93.27055555 | 12.24 | 03RW201-1 |
03RW201-2 | 38.70041666 | -93.27055555 | 22.31 | 03RW201-2 |
03RW201-3 | 38.70041666 | -93.27055555 | 25.11 | 03RW201-3 |
03RW203-1 | 38.74625 | -93.22355555 | 8.37 | 03RW203-1 |
03RW203-2 | 38.74625 | -93.22355555 | 6.23 | 03RW203-2 |
03RW300-1 | 39.751 | -94.8405 | 92.22 | 03RW316-1 |
03RW300-2 | 39.751 | -94.8405 | 71.56 | 03RW300-2 |
03RW300-3 | 39.751 | -94.8405 | 64.02 | 03RW300-3 |
03RW301-1 | 39.7794 | -94.7931 | 41.01 | 03RW301-1 |
03RW301-2 | 39.7794 | -94.7931 | 5.32 | 03RW301-2 |
03RW301-3 | 39.7794 | -94.7931 | 28.08 | 03RW301-3 |
03RW302-1 | 39.85939444 | -94.81692777 | 15.54 | 03RW302-1 |
03RW302-2 | 39.85939444 | -94.81692777 | 5.64 | 03RW302-2 |
03RW302-3 | 39.85939444 | -94.81692777 | 14.97 | 03RW302-3 |
03RW312-1 | 40.37565555 | -94.85741666 | 4.06 | 03RW312-1 |
03RW312-2 | 40.37565555 | -94.85741666 | 2.83 | 03RW312-2 |
03RW312-3 | 40.37565555 | -94.85741666 | 4.17 | 03RW312-3 |
03RW313-1 | 39.73430555 | -94.79005 | 25.04 | 03RW313-1 |
03RW313-2 | 39.73430555 | -94.79005 | 45.29 | 03RW313-2 |
03RW313-3 | 39.73430555 | -94.79005 | 34.94 | 03RW313-3 |
03RW316-1 | 39.75501666 | -94.28194166 | 8.66 | 03RW316-1 |
03RW316-2 | 39.75501666 | -94.28194166 | 34.92 | 03RW316-2 |
03RW316-3 | 39.75501666 | -94.28194166 | 6.29 | 03RW316-3 |
03RW317-1 | 39.70467777 | -94.88660555 | 27.72 | 03RW317-1 |
03RW317-2 | 39.70467777 | -94.88660555 | 28.71 | 03RW317-2 |
03RW317-3 | 39.70467777 | -94.88660555 | 29.85 | 03RW317-3 |
03RW318-1 | 39.55928888 | -95.03296944 | 19.32 | 03RW318-1 |
03RW318-2 | 39.55928888 | -95.03296944 | 6.46 | 03RW318-2 |
03RW318-3 | 39.55928888 | -95.03296944 | 6.31 | 03RW318-3 |
Please Advice.
Solved! Go to Solution.
I don't understand what you mean by "value less than 60%" - less than what? The 1900 column is confusing as well - what is that value? I see you have the expected result in Red is the only one that differs from the Site field. Can you provide some more insight on these figures?
Thanks!
Hi Jarrod,
1900 column is in percentage: if you see first value of 1900 column is 12.24(which is less than 70%) so the result should be exactly like Row 2 which is 03RW201-1 .then if we move down continuously like Row 1, then coming to Row 7: Column 1900 is 92.22(greater than 70%)so the example Expected result is the site name other than the Row 7 Site name which is 03RW316-1 instead of 03RW300-1 since the value of this Row is greater than 70% and jump to Row 22 which has 1900 column value 8.66 (less than 60%)...the expected result should have the value less than 60% within 3 miles of site column.
Please let me know it it helps.
Basically Expected result should have multiple conditions, one is replace site and value>70 with lesser than 60% within 3 miles
@jarrod adding to that condition, if it not less than 3 miles let do within 10 miles also..miles are dynamic.
Yes it would be helpful to have some more information as the distance between the two points you mention is about 30 miles, so I don't follow the specific need.
N.B. I was able to get the distance between the points by creating a centriod using the longitute and latitude columns provided and then joined Expected Site with Site which gives two centroids for one record which enables you to use the distance tool.
just saw your notes on miles being dynamic - that can be accomplished by bringing in a field and just referencing the field instead of the hard-coded mileage. adding a secondary limit is the same thing, just add another process to look for a larger distance, but what you'd probably want to do is just look for the largest mileage acceptable then sort by Distance before the unique to find the closest site. the workflow may need some tweaking for your specific purpose.
Thanks Jarrod, wonderful approach, mine was little long approach, really appreciate it..
can we tweak a little bit in the formula and try to avoid the result from the at the same location..like here Site column Lat and Long are same as the site in the expected result column, instead can we do any other lat and long? as in yellow.
In this approach, all the values greater than 70% from 1900 columns is gone:) and there is no replaced value in expected column.