## Recommended Posts

I am trying to make an excel spreadsheet that a person can input 2 8 figure grids and it will give the range and bearing from one to the other and then will calculate the elevation based of range and altitude difference.

The two grids are: 2309 1746 and 2270 1750 in cells C2, D2 and C2, C3 respectively.

I have the range working using this formula: =(SQRT((Power(C2-C3,2) + Power(D2-D3,2))))*10

The bearing formula I found is: =ABS(DEGREES(ATAN2(COS((C2))*SIN((C3))-SIN((C2))*COS((C3))*COS((D3-D2)),SIN((D3-D2))*COS((C3)))))*17.78

But it does not give me the correct bearing which is 5147 mils. Anyone any ideas?

Correct bearing for data you gave is 4904 mils. You can achieve it with this formula:

```=IF(ATAN2((D3-D2),(C3-C2)) < 0, DEGREES(ATAN2((D3-D2),(C3-C2))+2*PI())*17.78, DEGREES(ATAN2((D3-D2),(C3-C2)))*17.78)
```
Edited by BlackHawk

Correct bearing for data you gave is 4904 mils. You can achieve it with this formula:

```=IF(ATAN2((D3-D2),(C3-C2)) < 0, DEGREES(ATAN2((D3-D2),(C3-C2))+2*PI())*17.78, DEGREES(ATAN2((D3-D2),(C3-C2)))*17.78)
```

I'm not sure if this is because it's actually ARMA grids and not real life grids, but that formula is not giving me the answer that the ARMA compass & GPS are giving me.

EDIT: Just doing checks and sometimes it's spot on and other times it's not. I'll test it a bit more.

Edited by Thawk

A theoretical error I see immediately is that you are not using the center of the grids (55555 55555), however for just deriving direction and distance

it should not matter, as long as you always input equal digit grids.

Basically you have to check for the three cases, this is how i have it coded in my simple artycomputer:

```if(\$xa < \$xb && \$ya > \$yb){ \$directiond += 90; }elseif(\$xa > \$xb && \$ya > \$yb){ \$directiond += 180; }elseif(\$xa > \$xb && \$ya < \$yb){ \$directiond += 270; };
```

Basically you have to check for the three cases

That's what atan2 does.

It should but I remember still having to do this for some reason.

But I'm not that familiar with the excel functions to be honest.

It should but I remember still having to do this for some reason.

But I'm not that familiar with the excel functions to be honest.

It returns values between -π and π, so unless you want a negative bearing then you need to transform it for 3rd and 4th quadrant, for example with formula you posted.

The reason there is atan and atan2 is as follow, whenever you calculate the value of tan its true for 2 angles, the angle you calculate at as well as the back angle of that angle (Example):

Tan (45) = 1

Tan(45+180)= Tan(225) =1

So what they did is they restricted atan to provide you the values of only the first quadrant and the fourth quadrant which means your output angle will never exceed 90 and your range is [-90,90].

So what atan2 does it allows you to define what quadrant you want to be in by switching to rectangular coordinates (X,Y) , Here are a couple of examples:

atan2 (0.2,1) --> First Quadrant  80 Degrees

atan2 (-0.2,1) --> Second Quadrant 100 Degrees

atan2 (-0.2,-1) --> Third Quadrant    -100 Degrees

atan2(0.2,-1) --> Fourth Quadrant    -80 Degrees

As you can see you can define what quadrant your in, Which means you will get a range of [-180,180] if you wish to have the range of [0,360] simply have an "if" statement that checks if the value is negative and add 360 and you'll end up with only positive values.

--------------------------------------

Here is a quick plot using Matlab for all angles (Circles were plotted at different radii)

Blue: atan function

Orange: atan2 function As you can see it justifies what we said above.

PS. atan2 returns only the real value and eliminates the imaginary part (Might be useful for some future project )

Hope its useful

Edited by Bluef16

Thawk, i've put together my own code couldn't be bother to go through your frankly. I tested mine in game here is the code and screenshots to prove LINK , I tired it in 3 quadrants and worked i am postive the 4th will work eliminating all possible cases.

Edit: My code is for 10 digit grids if you'd like to input 8 then where i multiply X and Y by (-1) That need to be changed (-10) if it's 6 then (-100)..

Edited by Bluef16

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.