# MGRS Distance, Direction Calculation

1. ## 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. ## Re: MGRS Distance, Direction Calculation

Hi.

TANH represents the hyperbolic tangent in Excel.

ATAN is what you require.

Regards

3. ## 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. ## Re: MGRS Distance, Direction Calculation

Ha! You're welcome!

(Never heard of that one, myself!)

5. ## 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'?

6. ## 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. ## 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. ## 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. ## Re: MGRS Distance, Direction Calculation

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