+ Reply to Thread
Results 1 to 9 of 9

MGRS Distance, Direction Calculation

  1. #1
    Registered User
    Join Date
    09-19-2014
    Location
    Sarasota, Florida
    MS-Off Ver
    Enterprise, so new?
    Posts
    11

    Angry MGRS Distance, Direction Calculation

    I use the following formula to find the distance between two grid points on map.
    D2 would contain a grid input in this format
    35R LV 66008500
    F2 would contain another grid input in the same format
    35R LV 67008600

    =TRUNC(SQRT(((((VALUE(MID(D2,8,4)))-(VALUE(MID(F2,8,4))))*10)^2)+ ((((VALUE(MID(D2,12,4)))-(VALUE(MID(F2,12,4))))*10)^2)))

    This returns the hypotenuse of a right triangle essentially. The distances are correct, it value returned should be 1,414 (meters).

    Then I use the following formula to calculate the angle between the two points (based off the right angle triangle created by the measurements calculated above):

    =DEGREES(TANH((((VALUE(MID(A3,12,4)))-(VALUE(MID(C3,12,4)))))/(((VALUE(MID(A3,8,4)))-(VALUE(MID(C3,8,4)))))))

    THE PROBLEM:

    The answer returned is 43.6 degrees. Mathematically (or logically in my head) the answer should be 45 degrees.
    -If the base (adjacent) of the triangle is 1000 and the side (opposite) is 1000, then the tan (radian converted to degrees) should be 45.

    What am I doing wrong, or what am I miscontruing as the truth?

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: MGRS Distance, Direction Calculation

    Hi.

    TANH represents the hyperbolic tangent in Excel.

    ATAN is what you require.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    09-19-2014
    Location
    Sarasota, Florida
    MS-Off Ver
    Enterprise, so new?
    Posts
    11

    Re: MGRS Distance, Direction Calculation

    Thanks, that got me the 45 I was looking for. Appreciate the help. <--- Not a math guru.... better at World of Warcraft?

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: MGRS Distance, Direction Calculation

    Ha! You're welcome!

    (Never heard of that one, myself!)

  5. #5
    Registered User
    Join Date
    09-19-2014
    Location
    Sarasota, Florida
    MS-Off Ver
    Enterprise, so new?
    Posts
    11

    Re: MGRS Distance, Direction Calculation

    Let's throw another bone in the soup -

    Now I am trying to get an azimuth (grid) from the start point to the end point. The formula I have now works great if it is in the right hemisphere of the circle.. but when the angle hits 180 degrees or greater from the start, it returns an angle that is .. incorrect. In the same example used above - if I reversed the grid... the correct answer would be an azimuth of 225, but the angle measured by the formula is 45 degrees (correct in terms on the angle on the triangle created, but not in terms of the 360 degrees of circle I am actually working with.)

    D2 would contain a grid input in this format
    35R LV 67008600

    F2 would contain another grid input in the same format
    35R LV 66008500

    I impressed myself with getting what I have working - but am now at a wall. I have thought about if statements, but I am literally lost as to where to begin to make the logic work. The logic is how to make the angle accurate if it is going to be greater than 180 degrees from due north (up on a grid). To help visualize, this might help)

    (-) (+) l (+) (+)
    ------------------
    (-) (-) l (+) (-)

    (+) (+) and (+) (-) would provide the correct angle where (-) (-) and (-) (+) would need to have 180 added to the angle to provide the correct azimuth.

    Where (*)(*) represent the 4 digit groups of numbers, and the + and - represent the change in positive/negative distance. If the change to the first 4 numbers is positive in any manner than the angle measured would be correct, but if the change is negative the angle is not equal to the azimuth and needs to be adjusted by adding 180.

    I may have just confused the living hell out of someone, and I apologize.

    What is the best approach to finding this 'azimuth'?
    Last edited by sgtkling; 09-19-2014 at 08:46 AM.

  6. #6
    Registered User
    Join Date
    09-23-2014
    Location
    Fort Polk, LA
    MS-Off Ver
    2013
    Posts
    2

    Re: MGRS Distance, Direction Calculation

    You must be a FA Surveyor. I have ran into the same brick wall trying to do Azimuth and Distance from Coordinates using Excel several times over the years. Hence I was on this forum looking for one to determine Target to Gun AZ DIST. I did come up with the following form using a calculator:

    Calculator Azimuth and Distance.pdf

    AZIMUTH AND DISTANCE CLASS.pdf

    I also created a form and training for Traverse and Trig-Traverse. PM me your e-mail if your are interested.
    OBTW Retired SFC FA Surveyor

  7. #7
    Registered User
    Join Date
    09-23-2014
    Location
    Fort Polk, LA
    MS-Off Ver
    2013
    Posts
    2

    Re: MGRS Distance, Direction Calculation

    So at work tonight I created an Excel worksheet. I had to do one section for each quadrant due to the variables. You will have to use UTM instead of MGRS. As you know you will run into issues when you cross the two letter 100,000 Grid Zone Identifier with MGRS and you can drop the Grid Zone Designator.
    Two snapshots:
    First my worksheet:
    \1
    Second my worksheet showing the formula:
    \1

  8. #8
    Registered User
    Join Date
    12-14-2014
    Location
    Wainwright, AB
    MS-Off Ver
    Office 365, Office 2007, Office 2010
    Posts
    2

    Re: MGRS Distance, Direction Calculation

    Hi all

    I am wondering how to have excel calculate a bearing across map sheets. For instance I have a site in 12U VD 957 471 and another in 12U WD 080 519

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: MGRS Distance, Direction Calculation

    @nathan.brien: This forum can be rather strict about rules, like not posting your question in an old thread. Please start your own thread. If this thread is pertinent to your question, you are welcome to include a link to this thread for reference.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google maps +Get Direction +Return Times & Distance
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2010, 02:46 AM
  2. Excel Calculation Direction Question
    By paulabrozek in forum Excel General
    Replies: 4
    Last Post: 08-26-2009, 12:05 PM
  3. Distance/Time calculation
    By RC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2006, 09:25 AM
  4. [SOLVED] Excel Calculation Direction
    By wq in forum Excel General
    Replies: 1
    Last Post: 11-08-2005, 08:10 AM
  5. [SOLVED] time/distance calculation
    By KJO in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1