Help with excel equation

Hi
I’m very new to this but am trying to do a small app which calculates a bearing needed to take when starting and ending coordinates are input.
I have a working excel calculation which works but am struggling on how to convert this into an ai2 format.
I’ve got so far but stumbling with a bit of the math involved.
I’ve got the coordinates converted to radians using the inbuilt function which works correctly.
The excel calculation I wish to get into ai2 is as follows:
=MOD(ATAN2((COS(C3)*SIN(C6))-(SIN(C3)*COS(C6)*COS(C7-C4)), SIN(C7-C4)COS(C6)),2PI())*180/PI()
Here’s what I’ve got up to and think I’m very close.


Any help much appreciated

Please have look at this. In Atan2 you have a mistake on “y”

You should be getting getx under sin function. But you calculate all and you get sin of this result. And one more thing there is no PI in your equation.

I also make up below. I guess it works. I put PI = 22/7 but you can use PuraVida’s extension for using constants in your work (see https://puravidaapps.com/math.php). So it is easier to handle long equations.

1 Like

Thanks so much for your help.
I’ve progressed it somewhat but the final bearing result I am getting is still not accurate. Maybe I missed something.
This is what I’ve got it down to so far.

Pro-Tip:
You can right click in the blocks editor to “Download Blocks as Image” which is much easier to view in the forum.

1 Like

what about using a snippet?
An enhancement to the compass app to provide additionally the direction to a waypoint

Taifun


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

How about telling us what is supposed to be in cells C3, C6, C7, C4 so we can validate the formula?

Where did you get that formula?

Did you know that AI2 trig formulas are in decimal degrees, versus Excel formulas which are in radians?

How do you want your bearing to be expressed?:
(which direction is 0 degrees, and does it increase clockwise or does it increase counterclockwise?)

There is something wrong here. The value of PI is 22/7 = 3.14… But yours the result is 233.24… So that is why you get a different result. Please replace the Global PI = 22/7. Then you will have a better result

C3 = Lat1
C6 = Lat2
C4 = Lon2
C7 = Lon1

I’m converting to radians for the calculations then converting back to degrees to get my final heading result.

What I’m actually trying to do is a calculator for a space exploration game.
When I am flying over a planets surface I have my current lat/long co-ordinates on the ships hud and also know the co-ordinates of a point of interest.
My aim is to input all 4 co-ordinates and give me a heading I need to take to reach my point of interest.

I got the formula from an excel spreadsheet I downloaded.
The excel calculation works perfectly.

I thinkmthe main problem I have now after a few alterations is the Y calculation.
I’m pretty sure the X calculation is coming in correctly.


This what I have so far.
Also a few blocks not shown which initialise the variables.
I think the problem could be with the Y value being calculated incorrectly but not entirely sure.

blocks (1)

Plus this to convert the degrees input into radians.

What is the URL?

Did you read this?

Can’t find the original but I can share a saved copy for you to look at
https://1drv.ms/x/s!AjMKUS5ePlTr7lswkjmSvXBL24vf

I’ve also been using this page for information.

https://www.igismap.com/formula-to-find-bearing-or-heading-angle-between-two-points-latitude-longitude/

Update.
i think I’ve got it sorted.
will update with details after finished debugging and testing

Finally all working.
Thank you everybody for all your input, very much appreciated from a noob.
I have included my final set of blocks which worked for this calculation.
i didn’t need a lot of the variables and conversion parts as first thought needed, although they did help in debugging.
Also for some reason i had to switch the X and Y values in Atan2.
Works perfectly now.
Now i need months to design some error corrections and layout for the app.
Only the 2nd time i’ve used ai2 but really liking it.
Thanks again guys.

This should help with your Pi …
Pi
I have tested it, and it’s draggable into the Blocks Editor.

Thanks bud.
I was originally using that method but someone said it wasn’t accurate so changed it.
Think i will put it back in as it does seem like the most accurate way of calculating pi.

Last time out you were converting radians to degrees to get PI :wink:

So I was lol

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.