Spreadsheet formulae (OpenOffice, Excel etc)

[FRQ Charlie Bravo]

Hi all,

I use spreadsheets for so many aviation projects (I prefer the free Open Source programs of OpenOffice but Excel is OK) and thought I'd start a thread where we can share aviation tailored formuale.

First cab off the rank - How to calculate Feet per NM for a given profile:

=6076.1155/(1/(TAN(RADIANS(A1))))

Where:


A1 = the cell with your profile in degrees

1 Nautical Mile = 6076.1155 feet


For example on a 3.00 degree profile you should get 318.44 ft / nm or on a 3.23 degree profile (such as YGEL NDB or VOR Rwy 21) you should get 342.9 ft / nm.


Whilst this might seem overly pedantic it is the sort of accuracy required if you are going to then multiply by 20 different distances (i.e. making a TOD constant descent profile for varying situations).


If working out a pre-planned TOD table you now need to take into account the AD (or Threshold) elev and for uber-accuracy the position of the DME or ARP to the Touchdown Zone.



Now, because nobody reads an altimeter to the nearest foot you'll want to get user friendly results rounded to the nearest 10 ft. You can accomplish this automatically by prefacing your formulae with =ROUND (B2; -1) (where B2 = the number you wish to round). Also note that if you need to match the profile heights as given by DAPs or Jepps you can force it to be rounded up or down by replacing ROUND with ROUNDUP or ROUNDDOWN.


A simple table should come out looking like this:

http://i69.photobucket.com/albums/i66/Waylonparker/ProfileSpreadsheet-Result.png


Cat C aeroplane,
Jepp chart YABA 11-1,
Green shaded cells Check Heights or noteworthy points,
Red shaded cells DA/MDA,
Yellow shaded cell for data entry not printed,
Orange shaded cell not printed,
3 NM from approach plate not formula,
5 NM required 'ROUNDUP' to match approach plate

Or for those playing at home here are the formulae:

http://i69.photobucket.com/albums/i66/Waylonparker/ProfileSpreadsheet-Formulae.png

(I don't know about Excel but in OpenOffice you can turn this view on and off with Ctrl-tilde... this squiggly line is a tilde ~)

[27/09]

WTF???????????????????????????

Far too for me.

Whats wrong with a 3X profile (3 miles/1000') if pressurised e.g. TOD for FL190 equals 57 Nm plus slow down. At 180 kts G/S, ROD = 1000 fpm, 270 kts G/S, ROD = 1500 fpm, 360 kts G/S, ROD = 2000 fpm etc and rough interpolation for speed in between works well.

Or if unpressurised (G/S divided by 30) times height to lose in 1000's of feet, plus slow down. e.g. TOD for 9000 ft @ 210 kt G/S = 210/30 (or 21/3 for easier maths) X 9, i.e. 7 X 9 = 63 nm. ROD is always 500 fpm.

Both easy calcs to do in the head and no need to refer to a table.

[alphacentauri]

FREQ CB, mind if I suggest an easier formula?

You have a double divide in there and the formula would be easier if you present it like this

=6076.1155*(TAN(RADIANS(A1))

[Shagpile]

Measure with a micrometer, cut with an axe.

[jas24zzk]

Or if unpressurised (G/S divided by 30) times height to lose in 1000's of feet, plus slow down. e.g. TOD for 9000 ft @ 210 kt G/S = 210/30 (or 21/3 for easier maths) X 9, i.e. 7 X 9 = 63 nm. ROD is always 500 fpm.

I like it!.

I prefer time based descent plans, given I don't rely on GPS for primary navigation, although I often convert it back to distance, to give myself a double indicator for action time. Its probably a lil more complicated.

TOD = ETA- (Height to lose x2 (500 fpm)) - Circuit plan.

My circuit plan, is to be at the overfly height and settled by 3nm in most cases...a busy strip i might make it 5nm. So for a 120kt GS, its 2 minutes.....

Example. on 9000' with a 2000' overfly, 3 nm settle zone and 120 kts and expected arrival of time 52.

TOD= 52-14-2 = TOD time 36.

This really only works if you have bothered to do your maths enroute and revise your Estimates along the way.

Cheers
Jas

[Skystar320]

whats the problem with hiring an accountant?

[jas24zzk]

whats the problem with hiring an accountant?

Because they will come up with numbers to fly that exceed the accuracy of our instruments, and then charge you more than you saved

Simple really............you'll be left stressed and driving a gaewoo whilst they cruise off in a ferarri with your girlfriend

[FRQ Charlie Bravo]

FREQ CB, mind if I suggest an easier formula?

You have a double divide in there and the formula would be easier if you present it like this

=6076.1155*(TAN(RADIANS(A1))

Thanks Alpha,

You are absolutely correct. That formula started out as two different formulae which I clumsily made into one then forgot about. Personally I've got too many cells now to update so I'm leaving it in but it'd definitely provide for easier handling for anybody else starting from scratch.

FRQ CB
Self-taught - Like Steve Buscemi in the Wedding Singer (http://www.google.com.au/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&cad=rja&ved=0CEYQtwIwBA&url=http%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dlt9IJX0qI6o&ei=U6J-UJbkDbGaiQfg44CwAg&usg=AFQjCNF5s9FtTZHtG-8LIxGYfZhy5LeucA&sig2=CgR-vDioVWBovItcQ2S38g)

[gerry111]

jas, the correct spelling is Ferrari. I just checked my garage... :E

[kalavo]

Here's some..
http://williams.best.vwh.net/ftp/avsig/avform.xls

[alphacentauri]

Now for a harder one....

IAS to TAS

TAS =((B4*171233)*(((288+B5)-(0.00198*B2))^0.5)/((288-0.00198*B2)^2.628))

Where;
B4 = IAS (KT)
B5 = ISA VARIATION (including + or -°)
B2 = PRESS. ALT (FT)

EG; ALT=4000ft, IAS 200KT, ISA +15°
TAS = 217.8kt

see how you go :E

[Oktas8]

I've used SI units unless there is a suffix to say otherwise. So TAS is always in m/s, but TAS_kts means "expressed in knots". Similarly with other variables - e.g. radius in m, radius_nm in nm, etc.

In all cases, TAS = TAS_kts*1852/3600

Turn radius & Angle of Bank at rate 1:

Radius = 60/pi()*TAS
Radius_nm = Radius/1852
AoB_° = ATAN(PI()*TAS/60/9.8)*180/PI()

Turn radius at 25° angle of bank:

CPA, centripetal acceleration, = 9.8*TAN(25*PI()/180)
Radius = TAS^2/CPA
Radius_nm = Radius/1852

RMI lead with x degrees of turn yet to be completed:
(e.g. how much will the RMI lead the inbound course, if I have 60° of the inbound turn yet to do?)

Let DEG be degrees to go, RADIUS be turn radius and DME be distance from the navaid:

XTK, or distance from inbound course = RADIUS*(1-COS(DEG*PI()/180))
RMI lead = 180/PI()*ATAN(XTK/DME)

I wonder if it's worth putting a copy of the spreadsheet (https://docs.google.com/open?id=0B7M93Yy4R2iAOWtBX1lHcnAzbnM) on the internet?

Cheers,
O8

[Al Fentanyl]

(Robin Williams, Good Morning Vietnam):E

[skblz1951]

Excellent work!

I'd like to do an excel spreadsheet, to get X-wind and Head/Tailwind components similar to this:

Inputs:
RWY heading =
Wind direction =
Wind speed =

Answers:
X-wind = L xxx / R xxx
Head/Tailwind = HW xxx / TW xxx

Any sugestions? I just need the formulas.

Thank you

[Capn Bloggs]

I just need the formulas.
SOHCAHTOA is all you need. :ok:

[roundsounds]

How about:
30 degrees off runway QDM = xw is 1/2 (0.5) wind speed
45 degrees off runway QDM = xw is 2/3 (0.7) wind speed
60 degrees off runway QDM = xw is 9/10 (0.9) wind speed

[topdrop]

30 degrees off runway QDM = xw is 1/2 (0.5) wind speed
45 degrees off runway QDM = xw is 2/3 (0.7) wind speed
60 degrees off runway QDM = xw is 9/10 (0.9) wind speed
But, how do you work out if it's from left or right?? :suspect::rolleyes::ugh:

[john_tullamarine]

how do you work out if it's from left or right

reference both runway bearing and wind from a common datum, typically north, and figure the direction from the difference.

[T28D]

Wet your finger and see which side is cold !!!!!!!!

[maui]

Geez, I am glad I grew up before computers, (calculators even). Didn't even have to use the CR2 for this stuff.

Unpressurised is time based. Drop distance to circuit height divided by ROD=time before ETA to start down the hill.

Pressurised is profile based. 3 x height + slowdown.

No equipment other than preinstalled lump on shoulders. No batteries to go flat and it cant fall to the floor to be stomped upon.

And BTW who is looking out the window or attending to cockpit duties when you are fiddling and farting around with your tables?

Why is that some people try to make an easy task, complex.
:ugh:

Maui

[Howard Hughes]

I was once asked by an F/O "what's the formula for a visual approach"?:eek:

My answer: "Stick the airport 2/3rd's of the way up the windscreen, if it moves up pull back, if it moves down push forward", what could be more simpler?:ok:

[maui]

Works for me HH. But then again we are probably of similar vintage. Fossilised! :rolleyes:

Maui