Calling all Excel gurus

Luke UberDork
6/28/12 6:34 a.m.

Bear with me here...this gets convoluted quickly

I have two 'lots' of GPS coordinate data (latitude/longitude) in two different formats. One format takes 3 columns: - deg/min/sec, while the other takes 2: - deg/decimal minutes). I'm using excel to convert each GPS reading into a 3rd format, decimal degrees, which requires a separate formula for each existing data format, (i.e. different maths to go from deg/min/sec to "decimal degrees", and from deg/decimal minutes to "decimal degrees").

The 'output' data (decimal degrees) must remain in the same two columns (latitude and longitude). I.e. I don't want two lots of 'output' columns for data being transformed from their respective original formats.

My question is, is it possible to have a condition something like...

"If data appears in format A, use formula x, if data appears in format B, use formula y"

That way, GPS readings, (which may appear in either format), can be entered into the spreadsheet in their correct order, without having to re-write the "output" formula each time the format changes...if you know what I mean

Any help would be much appreciated.

icaneat50eggs Reader
6/28/12 7:22 a.m.

Yes you can do that.

Put the data in columns a b and c

In column d do your if then for if column c = blank use the formula for the 2 input format, else use the formula for the 3 column format. One catch is should you run into a deg min sec one where the seconds = 0 you must enter 0 not leave it blank

Luke UberDork
6/28/12 7:41 a.m.

Thanks! ...didn't think of doing it like that.

At the moment I've got the input data in 5 separate columns (3 for deg/min/sec, then a further 2 for deg/dec. mins).

pinchvalve GRM+ Memberand UltimaDork
6/28/12 7:44 a.m.

My experience with Excel is that yes, Excel will do it. The limitation of Excel is always the human user.

mad_machine GRM+ Memberand MegaDork
6/28/12 8:44 a.m.

This is the only Excel I am familier with

davidjs Reader
6/28/12 10:07 a.m.

If you look up "IF" statements, you can nest whatever you want...

Are the GPS being input manually, or imported in?

(Another trick is to do calculations or interim steps on a separate sheet, then just reference the answer).

Your big thing is figuring out how to discriminate between the different input types... can you throw up a quick example?

Luke UberDork
6/28/12 6:31 p.m.

Manually input, to be imported into another program later.

Here's a screen cap:

I think I can make '50egg's suggestion work, by consolidating the two GPS formats. Not technically 'correct' (ideally they'd be separate), but it ought to work.

friedgreencorrado PowerDork
6/28/12 6:49 p.m.

Since you've already found an answer, I'm going to crack a joke about your Excel saga.

http://www.youtube.com/watch?v=SwCYHXRB50U

Top comment on that vid sums up the show pretty well: "Drugs + Anime = Excel Saga."

icaneat50eggs Reader
6/28/12 10:09 p.m.

You don't have to consolidate to use my idea, just point the if thens to different cells

Luke UberDork
6/29/12 12:18 a.m.

Thanks for your help. I decided to consoldate them anyway and just change the headings.

I've ended up with: =IF(ISBLANK("seconds column"), formula A, formula B)

Likely not the most elequent method, but hey, it works .

@FGC...link blocked at work

friedgreencorrado PowerDork
6/29/12 2:44 a.m.

Luke wrote: Thanks for your help. I decided to consoldate them anyway and just change the headings. I've ended up with: =IF(ISBLANK("seconds column"), formula A, formula B) Likely not the most elequent method, but hey, it works . @FGC...link blocked at work

Sorry, dude..I forgot about the time difference. The link was to a silly J-pop song that was the opening theme to an anime. A very strange anime. I was trying to make a joke..

http://en.wikipedia.org/wiki/Excel_Saga

Luke UberDork
6/29/12 2:52 a.m.

I figured that much , will have to view it at home is all.