GPS Coordinates Format

TroyMorris

New Member
Joined
Mar 26, 2011
Messages
9
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Excel Workbook
FGHIJKLMNOPQ
3N\SDegMinSecLatE\WDegMinSecLong
4North28222628.37389West813258-81.54944444Link: 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 :ROFLMAO:

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!
 
Last edited:
Upvote 0
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
Code:
=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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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