# Project #1 - Distances and Headings to All Airfields

### #41

Posted 11 January 2011 - 06:51

Awesome work Tushka!

Jason

### #42

Posted 11 January 2011 - 07:04

Jason

### #43

Posted 11 January 2011 - 07:48

Looks like ID #52 is for Special places like Castles or Railway Stations and the odd Village.

Jason

### #44

Posted 11 January 2011 - 17:30

I am willing to have a go at headings (I work with excel quite a bit).

As an idea - the chart could also calculate aproximate flight times from one point to another if you just input crusing speed…

### #45

Posted 11 January 2011 - 18:43

Jason - can you post the excel sheet you are using curently in the old (2003) format?

I am willing to have a go at headings (I work with excel quite a bit).

As an idea - the chart could also calculate aproximate flight times from one point to another if you just input crusing speed…

Peter,

Yes I will post as soon as I am done as I am almost finished with the distances. It's a freakin huge spreadhseet with 444,889 entries! But the formula Tushka provided works like a charm!

I'm thinking we're going to need separate spreadhseets for each item since they are so big and now I am thinking once we have all this data we should create an app that is like a flight planner where you can calculate start and end points and get distance, heading and time aloft. That would be cool.

I'm sure as time goes on we'll be able to find new ways to use this data and make ROF more fun.

Jason

### #46

Posted 11 January 2011 - 18:53

The formula didn't end up needing nested IF's after all.

**A-B.map.headings.xls.zip**

**587.11KB**57 downloads

### #47

Posted 11 January 2011 - 19:02

In the formula

*ATAN2(Dx, Dy)*the result should be in radians, yes?

I have tried using this formula:

=IF(AND(($B7-E$4)<0,($C7-E$5)<0),(DEGREES(ATAN2(($B7-E$4),($C7-E$5))))+180,IF(AND(($B7-E$4)>=0,($C7-E$5)<0),(DEGREES(ATAN2(($B7-E$4),($C7-E$5))))+180,IF(AND(($B7-E$4)<0,($C7-E$5)>=0),(DEGREES(ATAN2(($B7-E$4),($C7-E$5))))+360,DEGREES(ATAN2(($B7-E$4),($C7-E$5))))))

*copy the above and paste it in cell E7, then copy the cell and paste to fill a range*

The problem is, if I leave out the DEGREES function it gives results with a deviation of up to about 3, so all headings are within in a few degrees of due North or South. Thus my assumption of it being in radians. The other issue is I am getting headings in excess of 360, so clearly need to reassess something

EDIT: Beat me to it again I will take a look at your formula and see what you did differently…

### #48

Posted 11 January 2011 - 19:15

=IF(($C7-E$5)>0;DEGREES(ATAN2($B7-E$4;$C7-E$5));DEGREES(ATAN2($B7-E$4;$C7-E$5))+360)

[edited to add explanation]

Using DEGREES(ATAN2(x,z)) gives values from 0 to 180 degrees in the East hemisphere and -180 back to 0 in the West (going clockwise)… so the logic is:

IF delta Z is positive THEN accept the direct value, otherwise add 360 degrees to convert the negative values to positive.

I worked out the logic with the simple tests of:

x,z angle

2,1 25.57

1,1 45

1,2 63.43

-1,2 116.57

-1,1 135

-2,1 153.43

-2,-1 206.57

-1,-1 225

-1,-2 243.43

1,-2 296.57

1,-1 315

2,-1 333.43

and a sketch of these data points.

### #49

Posted 11 January 2011 - 19:21

An application like you proposed Jason would be very usefull for long missions and coordination I am sure -> also for mission designers…

### #50

Posted 11 January 2011 - 20:21

I have done the headings for the A's and B's (using the same input sheet as for distances).

The formula didn't end up needing nested IF's after all.

Great! I'm finishing up the Distances. Yes, please use the same input sheets as for Distances to make it easy on me.

I will check your Headings when I am done with the distances.

Jason

### #51

Posted 11 January 2011 - 22:59

I shortened the distances to 1 decimal place and I added the Printed Map Coordinates to the sheet and a description of each type of item Airfield, Town etc. I think it came out well. I also made a few spelling corrections.

Excellent work on this Tushka! You deserve a plane. What one do you want?

I'm sure we'll find a good use for this info.

http://www.777studio...ances_Final.zip" onclick="window.open(this.href);return false;">http://www.777studio...t/ROF_Utilities … _Final.zip

Now on to Headings.

Jason

### #52

Posted 11 January 2011 - 23:16

Jason

#### Attached Files

### #53

Posted 12 January 2011 - 00:21

I have two Questions:

1.) I get a Divisable by zero error on the 0,0 entries. Is there anyway to fix this?

2.) What about the reciprical heading? How can we also calculate that? I do want to give both on my spreadsheet.

Jason

### #54

Posted 12 January 2011 - 01:10

I have used my own formulas with quite a few conditions.

There is everything calculated and the same cities top / left get a blank spot (I just marked it with a red cell so I could find any mistakes).

There were two cells that I had to redo manually as I couldnt add the needed conditions for the exact 0 / 90 / 180 / 270 headings (only happens on one location).

The inverse headings you get by default as at one point you have the city you are in as a base and when you change the starting point to your former destination it gets calculated.

You will immidiatly be able to see that when you open the chart - it alway on the opposite position relative to the blank cell (the red one).

Find it here

http://www.mediafire...g8erxb8pp6y6rym" onclick="window.open(this.href);return false;">http://www.mediafire...g8erxb8pp6y6rym

### #55

Posted 12 January 2011 - 01:19

Here's my chart for Headings using Tushka's formulas.

Peter - check mine out too. Let's compare notes tomorrow, I'm off to dinner with my girlfriend.

http://www.777studio...dings_Final.zip" onclick="window.open(this.href);return false;">http://www.777studio...t/ROF_Utilities … _Final.zip

Jason

### #56

Posted 12 January 2011 - 01:25

### #57

Posted 12 January 2011 - 02:11

,,,I have all the planes, including the pre-ordered Gotha… so whatever comes next.

Excellent work on this Tushka! You deserve a plane. What one do you want?

…

I don't contribute for the planes. I just want RoF to continue to be outstanding!

### #58

Posted 12 January 2011 - 02:19

Ok guys. I was able to make Tuska's Headng formula work in my spreadsheet.1) Fix by hand as either 180 or 360 depending on whether you should fly N or S. Dividing by zero offends the universe. I didn't provide escapes for these two poorly behaved conditions. (What *I* would do is fudge one of the positions by 10 cm ..i.e add .1 so there would be no zero in the first place.) Nobody will be concerned by an error of 10 cm, but that will avoid the problem in the first place. )

I have two Questions:

1.) I get a Divisable by zero error on the 0,0 entries. Is there anyway to fix this?

2.) What about the reciprical heading? How can we also calculate that? I do want to give both on my spreadsheet.

Jason

2) The reciprocal heading is found by adding or subtracting 180 degrees to get a number between 0 and 360. i.e. if the heading is less than 180 then add 180, if it is more than 180, subtract 180.

### #59

Posted 12 January 2011 - 03:06

Ok guys. I was able to make Tuska's Headng formula work in my spreadsheet.1) Fix by hand as either 180 or 360 depending on whether you should fly N or S. Dividing by zero offends the universe. I didn't provide escapes for these two poorly behaved conditions. (What *I* would do is fudge one of the positions by 10 cm ..i.e add .1 so there would be no zero in the first place.) Nobody will be concerned by an error of 10 cm, but that will avoid the problem in the first place. )

I have two Questions:

1.) I get a Divisable by zero error on the 0,0 entries. Is there anyway to fix this?

2.) What about the reciprical heading? How can we also calculate that? I do want to give both on my spreadsheet.

Jason

2) The reciprocal heading is found by adding or subtracting 180 degrees to get a number between 0 and 360. i.e. if the heading is less than 180 then add 180, if it is more than 180, subtract 180.

Tushka,

Can we make a formula to automatically add or subtract the 180? I'll make a separate sheet for the resiprical headings just so I have them.

Ok so what about time to destination? What is the average cruising speed of a WWI plane at say 2000m? 100kph?

Oh and I also want to convert the Distances to miles.

Jason

### #60

Posted 12 January 2011 - 03:12

Ok guys. I was able to make Tuska's Headng formula work in my spreadsheet.1) Fix by hand as either 180 or 360 depending on whether you should fly N or S. Dividing by zero offends the universe. I didn't provide escapes for these two poorly behaved conditions. (What *I* would do is fudge one of the positions by 10 cm ..i.e add .1 so there would be no zero in the first place.) Nobody will be concerned by an error of 10 cm, but that will avoid the problem in the first place. )

I have two Questions:

1.) I get a Divisable by zero error on the 0,0 entries. Is there anyway to fix this?

2.) What about the reciprical heading? How can we also calculate that? I do want to give both on my spreadsheet.

Jason

2) The reciprocal heading is found by adding or subtracting 180 degrees to get a number between 0 and 360. i.e. if the heading is less than 180 then add 180, if it is more than 180, subtract 180.

Tushka,

Can we make a formula to automatically add or subtract the 180? I'll make a separate sheet for the resiprical headings just so I have them.

Ok so what about time to destination? What is the average cruising speed of a WWI plane at say 2000m? 100kph?

Oh and I also want to convert the Distances to miles.

Jason

For reciprocal headings Jason, I refer to what I wrote in the bombing guide thread

To calculate a reciprocal for x:

360 ≤ x ≤ 180 = x-180

001 ≥ x ≥ 179 = x+180

I'm no excel guru though so I dunno how to put that in there.

### #61

Posted 12 January 2011 - 05:16

WWGeezer

### #62

Posted 12 January 2011 - 05:52

e.g for the heading from Acheler to (the first) Airaines look in E10 and find 273.2, while for the reciprocal heading look in F9 for the heading from (the first) Airaines to Acheler, 93.2. And note that those two numbers are exactly 180 degrees from each other (add 93.2 and 180 and get 273.2).

I left the reciprocals out of *my* table, figuring it was easy enough to use the "two twenty" rule to calculate the reciprocal in my head by adding two hundred and then subtracting twenty, or subtracting 200 and then adding 20, as the easiest ways to add or subtract 180 one digit at a time.

That's a trick taken from real pilots.

This rule doesn't do me much good in a N-17 or N-28 or Pfalz, etc., though.

### #63

Posted 12 January 2011 - 08:29

Ok here is what I am ultimately striving for. I know I'm crazy, but this is what I want to end up with. Just insert the formulas into my blank test sheet and I will do the rest no matter how long it takes me. LOLOLOLOLOL

Does anyone know how to insert 4 blank Rows under each entry 647 times quickly? Maybe some kind of Macro in Excel?

Test spreadsheet attached.

Jason

#### Attached Files

### #64

Posted 12 January 2011 - 08:46

The #div/0! are where the location is same on the top row and the left column. There is no heading here as the distance is 0 ->.

To make them dissapear in the chart just use the next formula (this one is for the top left location (cell E9):

=IF(AND(E$6=$B9;E$7=$C9);"";IF(($C9-E$7)>0;DEGREES(ATAN2($B9-E$6;$C9-E$7));DEGREES(ATAN2($B9-E$6;$C9-E$7))+360))

As a tip if someone might not know:

If you want to make easy work of it you can than select that cell - click copy and than select all the fields and just click paste - everything is done correctly than.

The selection is done very easily - no need to drag… Just click on top left cel, than move the sliders so you see the bottom right cell. Use SHIFT+click on that cell and you selected the complete field. Press paste and you are done.

Now press copy again as you got all the formulas selected now and just paste them on the next two sheets (and remove the calculations that are too many than on the far right side of the sheet.

EDIT: Didnt see your above post when writing this

### #65

Posted 12 January 2011 - 08:51

I can have a go at that if you like - just cant promise to have it done in the next few hours as I am at work.

### #66

Posted 12 January 2011 - 09:14

I allready have all the calcs going -> just have to insert the rows now and than copy the formulas.

### #67

Posted 12 January 2011 - 09:53

I'm a student of computer science and work part time as an assistant at my univeristy. Having the first out of three diploma exams tomorrow, so I don't have much time to spare. I also assist in teaching and I often need simple quests like this for the students to sharpen their programming skills. So please give us more problems like this to solve.

Second:

AFAIK Excel sucks. I don't now anything about it but i prefer real programming languages. So my weapons of choice would have been:

Excel -> CSV -> PERL -> CSV Excel

If i got you right you have N airfields and need the distance to the N other airfields and the heading. The first is easy, the second isn't hard either. You only have to get uniform triangles. Draw a circle around point A with a radius = distance between point A and B and using the rad measurement you would get a angle between them. After this is done, all you have to do is convert the distances to kilometers, miles, or whatever and the angles in rad to degrees.

I'm really sad that i don't have the time to write this program. Hopefully i can participate next time

so long

Mathias

Edit: just something i sketched up for this (didn't get attachments to work): http://blueboot.org/rof.pdf" onclick="window.open(this.href);return false;">http://blueboot.org/rof.pdf

### #68

Posted 12 January 2011 - 09:59

I used Macros and a little of my brain and I got it all sorted EXCEPT for the remaining formulas that I need for Heading B (Reciprocal), Distance (Miles), Time (100kph) and Time (70mph). I still suck as these formulas, but now I have the final format of the spreadsheet we can just fill it in. Once that's done, we can decide what exactly to do with the data. I know a lot of people don't like Excel, but for a desk jockey like me it's what I am most familar with. Let's just get the data and go from there. There's a bunch of it.

Get my spreadsheet here. http://777studios.ne...on_Info_All.zip" onclick="window.open(this.href);return false;">http://777studios.ne...F_Utilities/ROF … fo_All.zip

Jason

### #69

Posted 12 January 2011 - 10:26

It has gotten very big though so it should be treated with patience - it crashed my excel a few times when loading / saving…

Sorry for taking too long - upload took ages.

EDIT: I was again too late it seems - sry.

http://www.mediafire...zhju81u5zll0m6c" onclick="window.open(this.href);return false;">http://www.mediafire...zhju81u5zll0m6c

### #70

Posted 12 January 2011 - 10:36

Here the table - formated a bit and with the window locked a bit so it is easier to search.

It has gotten very big though so it should be treated with patience - it crashed my excel a few times when loading / saving…

Sorry for taking too long - upload took ages.

EDIT: I was again too late it seems - sry.

http://www.mediafire...zhju81u5zll0m6c" onclick="window.open(this.href);return false;">http://www.mediafire...zhju81u5zll0m6c

It's all good Peter. This is a team effort! I'll go check out your doc and formulas.

Jason

### #71

Posted 12 January 2011 - 10:50

Man your sheet rocks! Very nice! Awesome work. We accomplished something in 2.5 days that I have been wanting to do for over a year. Excellent work guys. Next project is to decide how to best use this data, but for now we can just stare at this mountain of information for fun. LOL

My only remaining issue is I'm not sure the speeds for the time measurement are best to use. What was the average cruising speed of a WWI aircraft? I just pulled those numbers out real quick. Suggestions?

Jason

### #72

Posted 12 January 2011 - 11:00

Can be done in Excel, but an application would be much better I think.

I usualy operate with different style of excel usage, so I havent yet made any of that sort, but am willing to have a go (need to learn it). I have seen it used in many sheets though so it can be done.

I guess the ultimate navigation tool would be a clickable map where you click on point A and than on point B and input your crusing speed - and get all the coresponding data than.

Also I can modify the current sheet to have two static crusing speed inputs(km/h, mph) of any speed you like and it will all be calculated accordingly on that - that one is easy to do.

Will do that now tbh - got lunch break anyway so…

### #73

Posted 12 January 2011 - 11:41

I will have a think about that selection thing later today - but I cant promise anything with that.

http://www.mediafire...xi6dd2l6mk29lck" onclick="window.open(this.href);return false;">http://www.mediafire...xi6dd2l6mk29lck

### #74

Posted 12 January 2011 - 18:23

This project is a fine illustration of the power of open-source-style collaboration, and the synergy of combining different skill sets.

### #75

Posted 12 January 2011 - 20:09

I have allready tried using filters, but it dosent turn out that well.

Going to try improving it tommorow or over the weekend.

### #76

Posted 12 January 2011 - 22:24

Here is my final Spreadsheet for this project and one that I will refer to at 777. You are all free to take this info and do what you wish with it. I refined some of the formatting.

Thanks to all who helped make this possible. Especially Tushka and Peter! You've both earned a plane. Just PM me what you'd like.

http://www.777studio...nal_1-12-11.zip" onclick="window.open(this.href);return false;">http://www.777studio...t/ROF_Utilities … -12-11.zip

What a bunch of interesting data!

Jason

### #77

Posted 12 January 2011 - 23:08

Peter is looking for ways to improve it, and I have a very small tweak (to show mph equivalent to km/h input and vice versa).

To implement this tweak in Peter's table without hurting anything, move the mph input stuff in his full copy from B5+C5 to B6+C6 using cut and paste. Then copy the tweaks from the example B5+C5 into the full copy B5+C5 area, and from the example B7+C7 to the full copy B7-C7 area (if you like it).

See the attached proof-of-concept example. It could be helpful for when British and French planes with airspeed indicators fly together.

#### Attached Files

### #78

Posted 12 January 2011 - 23:19

Haven't followed the thread, so if this was also suggested, pls ignore it - based on all these distances/headings, for mission makers a tool with the following inputs and outputs could be very useful:

inputs:

- source and destination airfields

- cruise speed for slowest aircraft in formation

- cruise HEIGHT

output:

- time to climb to cruise height

- total time to destination

I think these would be good in order to estimate where to place triggers. A robust mission would insert the slowest values and compute first set of results, and next the highest values and compute next set of results, and would place triggers so that they would cover both cases and of course all values in between.

</random thought>

### #79

Posted 12 January 2011 - 23:20

What's the rush?

Peter is looking for ways to improve it, and I have a very small tweak (to show mph equivalent to km/h input and vice versa).

To implement this tweak in Peter's table without hurting anything, move the mph input stuff in his full copy from B5+C5 to B6+C6 using cut and paste. Then copy the tweaks from the example B5+C5 into the full copy B5+C5 area, and from the example B7+C7 to the full copy B7-C7 area (if you like it).

See the attached proof-of-concept example. It could be helpful for when British and French planes with airspeed indicators fly together.

Tushka,

You guys can continue to tweak if you want. My initial goal was to get the data and at least make it readable. If the table or format is further refined go for it and feel free to post it. If I find it supersedes what I have posted I will replace with yours.

My overall goal with this stuff is to give you 3rd party guys information that you can somehow apply to the ROF universe and conversly maybe their will be info or data we uncover during our researching process that the team may be able to use at some point.

And I'm always in a rush. This project alone has been a huge distraction from my daily routine the past three days. We and even I constantly move from one fire to another.

Jason

### #80

Posted 13 January 2011 - 03:04

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users