Rainy day tasks

[Piper.Classique]

Greetings to all ppruning in this foul weather. I am trying in my fumbling way to automate a spreadsheet that will give me heading to fly (true is fine) and groundspeed given TAS, wind, and track required. My problem is not the formula as such but how to insert the variables on an .xls spreadsheet. Is there anyone here with a higher geek rating who could show me an example?
The actual spreadsheet is done as far as pretty goes but isn't much use as it doesn't yet do the calculations. Here is the link to it in my dropbox....

A6 Plog (https://dl.dropboxusercontent.com/u/60313145/Flight%20Planning/A6%20plog.xls)

In case anyone is feeling kind. I realise I will need two cells for wind speed and direction, but that is as far as my brain takes me.

[Jan Olieslagers]

Mine has a second tab with a list of possible waypoints.
When I reference these in the main calc sheet, their coordinates are looked up from the list and serve as the base for calculations.

[Piper.Classique]

Sorry Jan, I'm not following you there. I just want my spreadsheet to solve a wind triangle using the cosine rule, given the known vectors of track and distance required, wind, and TAS. But I don't know how to integrate this into a spreadsheet. The layout suits me, but I don't want to have to do a manual calculation. I can't possibly put in a list of waypoints in advance of knowing where I intend to go.

[Been Accounting]

Try Ed Williams' Aviation formulary. I think it has some examples already set out in excel. http://williams.best.vwh.net/ftp/avsig/avform.xls

[Saab Dastard]

You may struggle to do an iterative calculation like this one accurately in a spreadsheet.

Not wanting to teach grandma to suck eggs, but have a look at this thread from last year: http://www.pprune.org/private-flying/509450-navigation-flying-triangle-velocities.html

SD

[BackPacker]

Or is your question just a basic Excel usage one?

Excel doesn't do "variables" as such. Instead, you put a value in a cell. And that cell is referenced later on.

Here's an example. Your "advertised" stall speed (Vs, or the bottom of the green arc) is in cell B2. You now want to know your stall speed under a certain load factor (which, itself, may be derived from the angle of bank.) Say, the load factor (G) is stored in cell B3 and the result should be in cell B4. Then B4 should have the contents

=B2*SQRT(B3)

It's that simple. If the contents of a cell start with an equal sign, it means it's a formula. And within that formula you can use the normal mathematical operators (+, -, *, / and so forth), functions such as SQRT, SUM and a whole library of others, numerical values and references to other cells.

There is a little known catch though. You can refer to a cell as B2, or as $B$2 (or as B$2 or $B2 - it depends on what you want to achieve). In the first case, if you copy and paste cell B4 (the formula) to cell C4, then the formula will automatically be changed into =C2*SQRT(C3). In the second case, $B will remain $B despite the downwards copy & paste. So if you are referring to cells that hold constants or one-time values in your formula, best to refer to them as $B$2 so if you copy & paste any formulas, the cell reference will remain unchanged. But if you copy and paste formulas that refer to values that are stored in tables themselves, it's better to refer to them as B2. The dollar sign sort-of "locks" the row or column, so it doesn't change when doing a cut&paste.

[Jan Olieslagers]

If BackPacker's answer is what you needed, let me re-phrase it: if you need extra intermediate variables, the only way is to have an extra column for them. Mind you these "intermediate variables" columns would be confusing the presentation, BUT they can be hidden from view (select column(s) , right click, "hide" ).

In my own, only columns A-H-K-L-M-U-V-W-X show, all others are hidden.

[Piper.Classique]

Thank you all for the help and advice. Yes, it is really the use of formulae in excel that is confusing me. But I am easily confused when it comes to excel.......
Jan, could you let me see a copy of your spreadsheet? I would really like to get to grips with this, as I am trying to keep my brain from freezing up with old age, not to mention the speed of use if I ever get it done. Saab, thank you for the link, but not really what I am looking for.

[500ft]

Piper Classique

Did you find what you were looking for?

If you open excel Press ALT-F11, this will open a visual basic screen, then choose insert > module.

Then paste the following


Function GROUNDSPEED(TAS, WINDSPEED, WINDDIRECTION, TRACK)
GROUNDSPEED = Sqr((TAS * TAS) + (WINDSPEED * WINDSPEED) - (2 * TAS * WINDSPEED * Cos(WorksheetFunction.Radians(TRACK - WINDDIRECTION + 180))))
End Function

Function HEADING(TAS, WINDSPEED, WINDDIRECTION, TRACK)
HEADING = TRACK + WorksheetFunction.Degrees(WorksheetFunction.Asin(WINDSPEED / TAS * Sin(WorksheetFunction.Radians(TRACK - WINDDIRECTION + 180))))
End Function


Then close the module and visual basic.

You will now have 2 user defined functions which you can use for your formulas. You can use these like other formulas.

So if you have 100knts TAS, 20knots windspeed, 030 wind direction, 260 track.

=GROUNDSPEED(TAS, WINDSPEED, WINDDIRECTION, TRACK)
=HEADING(TAS, WINDSPEED, WINDDIRECTION, TRACK)

If you type =HEADING(100,20,030,260) the you will the answer 268.8
If you type =GROUNDSPEED(100,20,30,260) you will get 88.5

Just replace with references as required eg. =HEADING(B6,B7,B8,B9)

You can also use fx in the formula bar to remind you what goes where.

You will need to save the excel as macro enabled or the functions will not save

[Jan Olieslagers]

It is certainly received here with great interest, 500ft, many thanks!

[Piper.Classique]

Oh, nice! Thank you very much. I must say the task is far more complicated than I first thought. I've used Jan's spreadsheet as a basis, but will continue with 500's as well. Excellent stuff.

[Jan Olieslagers]

but will the VisualBasic stuff work in OpenOffice/LivreOffice too?

[500ft]

but will the VisualBasic stuff work in OpenOffice/LivreOffice too? Not sure what functionality OpenOffice /LiveOffice has, wouldn't be surprised if it didn't work.

You can just write formulas into the cells rather than creating functions. Just makes it bit more cumbersome.