Converting Latitude/longitude To OSGB Grid Ref
Jul 31, 2008
I'm using excel to try and sort by distance a list of wind farm locations from a location i put in.
This should be relitavely straight forward, however there are a few elements that make it more complicated.
I am getting a list of wind farm locations from the internet, the BWEA site. The latitude and longitude data copied over is in text. I'm using Excel '07 but it still requires me to butcher the numbers out of it with several columns of REPLACE formulas, it isn't elegant and may well be wrong.
The second and harder problem is that I have to first convert the latitude and lonitude values into OSGB grid references. So far i have found the OS maps excel converter, but it only converts one value at a time. I need something that will convert all of them at once (i don't want to go through and individually type in each distance).
After that it is possible to use basic trigonometry to find the distance between them and any grid reference i put in.
View 9 Replies
ADVERTISEMENT
Dec 12, 2009
I'm currently working on a project with data that includes columns for both latitude and longitude which I will need to convert from its current form into decimal degrees. The problem is that the coordinates are in the format DDDMMSS followed by a single letter indicating the direction, which means I have to first extract proper degree minute second format before I can do anything. Further complicating the problem is that each of the entries does not necessarily have all of the components I just described (eg some of the latitudes only have degrees and minutes but not seconds) and also if a longitude is less than 100 degrees, the third digit is not used in the data. There are over 2000 records so doing the conversion by hand just won't work and I've only got a few days to present this part of my project anyway.
I'm looking for a formula or series of formulas that will reliably convert something like 1083000W into -108.5000 when the last two zeros may or may not be there and the first digit may also be missing.
View 11 Replies
View Related
Jul 15, 2014
I currently have an excel file of around 40.000 records with their zip/postal codes accompanying addresses in countries all over Europe, I would like to convert these into latitude and longitude coordinates.
View 2 Replies
View Related
Feb 5, 2009
Some of the data I was given has latitudes and longitudes which are valuable to me but some of the information was entered incorrectly and the lat and long columns have only zeroes for some entries. I used some VBA to sort out all the ones that have zeroes in the lat and long columns because I noticed in a comments block some of them have the latitude and longitude placed in the comments instead of the designated spot.
An example of one of the comment cells is this:
Search and Rescue 30NM S MARTHA'S VINEYARD 40-37.4N 070-43.3W MEDEVAC OF 35YOM E
As such the problem I am having is figuring out how to search through the cells for ones that have a lat and long in them since there is other information and numbers in the comment cell. Keep the cells that have latitudes and longitudes and get rid of the rest. The red highlighted information is what is valuable to me. Another problem is not all of the latitudes are in that format sadly as can be seen by this example cell:
SAR Unreported Vessel/ 42 01. N 067 25. W CLOSED AREA II/041527ZAUG06
If anyone has any insight/references into how I could go about doing this I would pretty much owe you my life since doing it manually would take ages for myself and my group on this project.
View 5 Replies
View Related
Mar 19, 2009
I am using Excel to calculate distances from latitude and longitude GPS positions. These positions have a format as follows:
S36 39.113 E174 25.945
These have been imported into a column, and using VBA I wish to separate latitude and longitude into two other columns. In both cases the minutes will eventually have to be express as decimals of the degrees. I don't mind if numerous columns are used as stages to get the final answer which is 36.234678 in one column and 174.15567 in the other.
View 14 Replies
View Related
May 18, 2011
I have a list of 800 lat/long coordinates. I need to see how many of those 800 are within 3 miles of another list of 7,000 lat/long coordinates.
I have found the following formular to find the distance between 2 lat/longs:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1))
*SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756
Is there a formula that would allow me to run each of the 800 Lat/longs against the list of 7,000 and indicate how many on the original list have a match within 3 miles?
I was thinking a combination of countif with an array formula, but I can get the order of operations correct.
View 9 Replies
View Related
Nov 16, 2009
I have a spreadsheet with a column of Longitude values like:
A
172828.383E
I want to somehow split the cell into new individual cells so it will be like this:
A B C
17 28 28.383
View 5 Replies
View Related
Mar 23, 2013
I have list of address with zip code and Latitude and Longitude information. I am trying find a central locations, which will act as hub for rest of the address, by comparing distances from on locations to another.
Basically i need 2 things
1) How to identify the central locations
2) How to groups other locations around the central locations
Criteria here is distance
View 1 Replies
View Related
Feb 25, 2014
I have more than 1000000 coordinates with heights to sort through. The aim is to be able to give a specific radius and check all coordinates within this radius if the slope is more than a maximum slope. If this is the case it need to put the value (in this case) 100 in a new column. The reason for this is we have a reasonable flat terrain but the entire area is filled with Anthills. I need to sort the data. Normal ground points (No Anthills) should be labelled/coded as 200 and anthills as 100. This will allow my program to know the difference between the ground and anthills. In the tab "Input Sheet" I have a small portion of co-ordinates starting from row 8 to row 53 (this will have to extend all the way down to the last row in excel). I need to copy each row starting with row 8 (C8:E8) and paste it in row 2 (C2:E2). Column H indicates if the points are forming an anthill and the code needs to change. I have my final answer in the tab "Final Answer" that I require for my program. Is there any way I can write a VBA code that will check all the point instead of doing it manually.
View 4 Replies
View Related
Feb 2, 2014
I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.
After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works! However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set.
File attached with sample data sets.
View 14 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Oct 24, 2013
I created a table where based in several competences anyone can evaluate performance and potential. My issue comes when based on that result (colum Y in "Perf&Pot" Sheet) I want to export that to a nine box matrix (Sheet 2 "grid" in the attached excel). As could be several people in the same box I wasn't able to com up with a formula that solves this.
Pasta1.xlsx
View 1 Replies
View Related
Jul 9, 2014
I'm am trying to extract latitude from a cell that has the longitude and latitude separated by a space followed by a space and addition numbers. Here are three examples:
-89.8498682 35.467965 0
-89.849981 35.468031 82
-89.8500404 35.4681497 107
I tried: =MID(B262,LEN(B262)-LEN(C262),FIND(" ",RIGHT(B262,LEN(B262)-LEN(C262)))) where B2 is the source and C2 is the extracted longitude and it seems to work some of the time, but here is an example of when it doesn't:
-89.8499246 35.468019 0-89.8499246 6 35.46801
View 3 Replies
View Related
Sep 9, 2009
I have two lists of data that are next to each other an example is below:
A B C D E F g
1 Lattitude Longitude Speed Lattitude Longitude Speed
2 52.49 -2.97 30 52.49 -2.97 30
3 52.48 -2.96 30 52.48 -2.97 40
4 52.48 -2.97 30 52.48 -2.96 30
This table goes right down to cells 20099 so it is a huge set of data as you can see i have sorted both sets of data by latitude from highest to lowest.
My problem is that I need something that will go through the data and highlight locations that are identical with different speed limits. So if both the latitude and longitude are the same then compare the speed limit.
So in the above example cells A4 and B4 are the same as cells E3 and F3 and the speed limit is different so I would like this to be highlighted.
However if the Lattitude and longitude is the same and so is the speed limit it does not necessarily need to be highlighted
I can get this to work but only if the lat/longs that are the same is in cells next to each other. I do not know where to start if the identical locations are in separate parts of the spreadsheet.
View 9 Replies
View Related
Aug 10, 2007
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.
I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .
And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.
View 9 Replies
View Related
Nov 26, 2007
I want to have 2 cells where I can enter 2 numbers (length & height) so that a grid can be plotted. For example, a big sheet of paper size is length 30" x height 8", if first number is 10" (length) is entered, it will plot 3 lines across 30" (cos 30" divided by 10" equals to 3) and if I enter 2" (height) on 8", it will then cut it into 4". Attached a file for better understanding.
View 14 Replies
View Related
Jun 15, 2009
I'm not worried about the order of the data. What I want is information on turning a grid into a list.
View 4 Replies
View Related
Jan 16, 2013
I'm creating a fairly large risk analysis spreadsheet which uses a fairly standard 5x5 risk matrix.
I need a function or a formula which refers to the score a cell in a 5x5 grid, if given the row and column values, like this (where the columns are probability and the rows are impact):
VL
L
M
H
VH
VL
1
2
4
6
7
[Code]....
View 3 Replies
View Related
Jun 29, 2013
Example.xls
Within my attachment, I have a grid of data, Row A are headers, and beneath, various corresponding dates, there are no row headers.
I'm trying to do a lookup so that column header is returned bases on a exact cell value.
Example: inputting 03/03/2014 will return "Week 3"
Week 1|
Week 2|
Week 3|
Week 4
07/07/2013
|14/07/2013
|21/07/2013|
28/07/2013
[Code] ......
View 5 Replies
View Related
Mar 6, 2014
I'm trying to link objects and pricing to groups via a combo box. If the user selects Group A from the combo box and enters Object A on the line item, I want the price associated with Object A and Group A to populate.
Group A
Group B
Group C
Group D
Object A
$1
$3
$5
$7
Object B
$2
$4
$6
$8
Object C
$3
$5
$7
$9
Object D
$4
$6
$8
$10
View 9 Replies
View Related
Nov 18, 2008
I have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.
I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.
View 4 Replies
View Related
Aug 13, 2008
about using list as a datagrid type. I was able to research some samples and applied it on my own, but unfortunately I am missing something again here. The data on the worksheet are not being populated when my range is on the AA column. It works with A1 column but not the far ranges. Im not sure about the limit of range anyway here is the
View 14 Replies
View Related
Oct 15, 2008
I'm having an issue with VLOOKUP, I'll keep it short as I don't think I need loads of details because I have done some troubleshooting and believe got to the cause of the problem... I just don't know how to fix it!
View 4 Replies
View Related
Mar 9, 2009
This is to inform you that I frequently use toggle grid tool to make my worksheet white/plain,doing this with the help of mouse is eating lot of my time which i really feel anonying/pain.
I would like to have a short cut key for toggle grid which lies in Forms toolbar.
View 9 Replies
View Related
Apr 23, 2009
I am trying to modify the code below to toggle the snap to grid on/off or off/on and is giving me an error.
Sub ToggleSnapToGrid(Optional Dummy As Long)
With Application.CommandBars.FindControl(ID:=549)
If .State = 0 Then
.Execute
Else
.State = 1
End If
End With
End Sub
View 9 Replies
View Related
Mar 23, 2007
I hope someone can help me with this, Ive attached a copy of an example spreadsheet that has the same requirement i need for another one i am creating.
Sheet 1 has the user input area, where the user enters the data they want stored.
All i need is to be able to transfer the data into the correct area on the grid on sheet 3,
View 5 Replies
View Related
Mar 3, 2008
Looking to expand grid in Excel 2003,
View 4 Replies
View Related
Apr 5, 2009
I have tried to make cartesian coordinates (X)and (Y) using excel 2007. now I want to represent some shapes (circles, squares, triangles, and so one). My problem now is with formating those shapes. I use this path (Insert > Shapes),, but the shape I add cover the grid,
I want (for example) a circle but I need only its circumference, and without covering the grid by its area. the same for other shapes.
View 3 Replies
View Related
Apr 20, 2009
How can I set up my sheets so that the background and grid are isolated, in other words only the background, the grid, and my data shows on the sheet, the rest will be blacked out. I need to email only the certain area of the worksheets.
View 3 Replies
View Related
Oct 3, 2013
Basically, I need to extract data from a grid/table (11 columns wide) and transpose it into a list for use in a pivot table. It like sort of reversing a pivot table to access the original data but I only need 5 of the columns repeated for each occurrence.
I have read many internet sources but they don't seems to fit my needs. Here is a solution from a similar question posted on StackOverflow entitled "How to “flatten” or “collapse” a 2D Excel table into 1D?"
The attached simplified mock-up explains what I am trying to do. I have colour coded the data to understand. The number of rows (10) will always be the same but the number of columns (Branches in this case) can extend for many columns.
View 4 Replies
View Related
