GPS Coordinates Format

[TroyMorris]

Hi everyone

I'm trying to put together a sheet where a user can enter data into four cells (N,S,E,W via drop down list in the first box, degrees, minutes and seconds in the remaining three).

Ie. S ## ## ##.##

The formula =G4+(H4/60+I4/3600) then converts the data to a decimal value which Google Maps will accept.

I do this for both latitude and longitude coordinates so I am left with two decimal values in cells J4 and O4 respectively.

What I need to do (firstly) is have excel check the value in cell F4 (which the user enters via drop down list, N or S.

Based on that value, I need the result in J4 to be a whole number or "-".
Ie. ##.###### would be a North value, -##.###### would be a South value.

And likewise for East / West values.

Once that's done, I need excel to grab both values (for example, S & E) and format them in cell Q4 so it represents a link which the user can click on to take them to a google map of that location.

I hope that I've explained that clearly... it sounds right in my head...

If someone could please help me with those two formulas that would be fantastic!

Cheers

Troy

 

[AlphaFrog]

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q
3	N\S	Deg	Min	Sec	Lat	E\W	Deg	Min	Sec	Long
4	North	28	22	26	28.37389	West	81	32	58	-81.54944444		Link: Google Maps
Sheet

 

[TroyMorris]

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q
3	N\S	Deg	Min	Sec	Lat	E\W	Deg	Min	Sec	Long
4	North	28	22	26	28.37389	West	81	32	58	-81.54944444		Link: Google Maps
Sheet

Hi Alpha

Thanks very much for the speedy reply!

Couple of further questions on this one... not sure if this is just me being an idiot... but we'll see

Your amended formula for J4 & O4 appear to only change the values of J4 from 28.37389 when North is selected, to 27.62611 when South is selected... and the same occurs with the Longitude... this is throwing the map out obviously... when selecting the South drop down, it should change the value to a negative ...?

Secondly, how do I hide the "Link: Google Maps" text in the rows which haven't had data entered, only to be populated once they are...?

Cheers, I really appreciate your efforts!

 

[AlphaFrog]

Your amended formula for J4 & O4 appear to only change the values of J4 from 28.37389 when North is selected, to 27.62611 when South is selected...

Don't know why the value would change with North\South.
This part of the formula...
* IF(F4="South",-1,1)
...simply multiplies the result (Latitude) by negative one if South is in cell F4. Otherwise it multiplies the result by positive one. How could that change the value of the result?

These red parenthasis are critical. You didn't change them did you?
=(G4+H4/60+I4/3600) * IF(F4="South",-1,1)

Secondly, how do I hide the "Link: Google Maps" text in the rows which haven't had data entered, only to be populated once they are

Cell Q4

=IF(COUNTBLANK(F4:O4)," ",
    HYPERLINK("http://maps.google.com/maps?f=d&saddr=&daddr="&J4&","&O4,"Link: Google Maps"))

That means all cells from F4 to O4 have to have a value in them before the Link is displayed in Q4. If you want to leave an entry blank, you would have to put at least a zero in the cell so the link is displayed.

 

[TroyMorris]

Ahhhh it was me just being an idiot... not sure how I managed to change the parenthesies... but all working now...

Thanks very much for your help mate... this forum is fantastic!

TM