How to create dynamic output tables

[A_Mediocre_Kangaroo]

I'm looking for help to make a program that will allow me to spend less time with a pen and a calculator for my school work.

The calculations will basically be starting with the coordinates of two or more points and working out all their directional angles and distances relative to eachother.



Example:

A: 75612.502 N , 1412.162 E

B: 75637.421 N , 1422.692 E



Directional angle of AB = ArcTan( (1422.692-1412.162) / (75637.421-75612.502) ) = 25.4527 Gradian (22.9074 degrees)

Directional angle of AB = ArcTan( (Be-Ae) / (Bn-An)



Distance AB = Square root of ( (1412.162-1422.692)^2 + (75637.421-75612.502)^2 = 27.0525

Distance AB = Square root of ( (Ae-Be)^2 + (An-Bn)^2 )



I'm mostly using gradian instead of degree. 400 units instead of 360 units in a full circle. 1:0.9 ratio

There is also the four quadrants to take into consideration, if delta N or E is negative the angle output needs some more work.

If both delta N and delta E are positive the formula gives the right answer, if dN is negative add 200g(180°), if dN and dE are negative add 200g(180°), if dE is negative add 400g(360°)
59fb06790eeae_inputoutput.jpg.13bbea6feb7a0b0f16df8105860dee93.jpg

I want to adjust automatically for the number of points I need to calculate. Like how giving it 2 points gives me 1 line of output 4 points give 6lines of output and giving it 20 points somewhere around 200. The output field would be huge sometimes (and that's mostly the reason I want to make this in the first place) but not always.
I think I can manage the formula and get the correct output for two points manually but I want it detect how many points there are then output each calculated relation dynamically.

Is this done by having one huge cell full of IF's, AND's THEN's that output to an area perhaps?
As someone who a few hours ago only used excel to make non interactive information lookuptables I must admit there might be a slight need for spoonfeeding me information.

[José Augusto]

Hi
See this
In the range 'Pts'!B3:D8 put the Name, North and East coordinates of the points.

In a new sheet Angle use this approach (table with two entries)

B3:B8 -> names of the points
C2:H2 -> names of the points

Use this formula in C3 and copy forward and down

Please Login or Register  to view this content.

Use the same approach to get the distances with the following formula

Please Login or Register  to view this content.

See the file

[A_Mediocre_Kangaroo]

That is a great solution! I'm not sure if I'm just really new to tables but I was a bit blown away when I realized the approach you had taken to the output tables! Beauitful! THANK YOU!!!!

[José Augusto]

You are welcome.

Do not forget to mark this thread as solved.