Any Spotters Good With Mathematics?
Thread Starter
Join Date: Dec 2014
Location: North Yorkshire
Age: 36
Posts: 362
Likes: 0
Received 0 Likes
on
0 Posts
Any Spotters Good With Mathematics?
Afternoon
I have this daft rule when I'm spotting...each airframe copped is worth 50p, then I can decide at the end of it if the total number of new sightings/scrapes was worth the cost of the trip. It might sound daft at first glance, but I find it a clever little way of looking at it.
What I would like to do though is develop that further, for example a Tanzanian bizjet is going to be worth a lot more than 50p whereas an easyJet Airbus is going to be worth less (from the point of view of a Brit of course), so I want to create a Spreadsheet where I can input a reg and it will calculate the value based on certain parameters, but I'm simply not clever enough.
Probably impossible but if you don't ask...
I have this daft rule when I'm spotting...each airframe copped is worth 50p, then I can decide at the end of it if the total number of new sightings/scrapes was worth the cost of the trip. It might sound daft at first glance, but I find it a clever little way of looking at it.
What I would like to do though is develop that further, for example a Tanzanian bizjet is going to be worth a lot more than 50p whereas an easyJet Airbus is going to be worth less (from the point of view of a Brit of course), so I want to create a Spreadsheet where I can input a reg and it will calculate the value based on certain parameters, but I'm simply not clever enough.
Probably impossible but if you don't ask...
Aircraft nationality could be extracted fromthe reg by way of a look-up table on a seperate sheet for the formula to reference - this is easy. But where would you look-up the cross reference between aircraft reg and type? Is this available in a form that can be "consulted" automatically? If not then you're probably going to struggle.
PDR
PDR
Join Date: Aug 2002
Location: London (Babylon-on-Thames)
Age: 42
Posts: 6,168
Likes: 0
Received 0 Likes
on
0 Posts
What I would like to do though is develop that further, for example a Tanzanian bizjet is going to be worth a lot more than 50p whereas an easyJet Airbus is going to be worth less (from the point of view of a Brit of course),
Join Date: Oct 2007
Location: Wherever I go, there I am
Age: 43
Posts: 807
Likes: 0
Received 0 Likes
on
0 Posts
In my head I see a two sheet spreadsheet. The first sheet handles the calculations for you, while the second sheet is a list of the types, company, and rego.
You could have a grouping on sheet one that assigns a cost based on any grouping you want (UK local, EU local, the Americas, etc.). Just remember, the more groupings you have, the more complicated the formulas.
The most basic formula I can think of for the setup above and where the aircraft rego is listed on Sheet 2, column A from rows 2 to 10, would be:
=COUNTIF(Sheet2!A2:A10,"G*")
This would count all those registrations that begin with G. You could then use a multiplication formula to multiply that number by whatever value you have assigned to local aircraft, for example:
=B2*0.5
or
=B2*E2
where E2 contains the value you want the number of registrations multiplied by. This allows you to change the value over time, without having to redo all the formulas.
From there... well it depends on what information and groupings you want to see. You can keep using the above formula for every country (that'll get exhausting quick) or you could begin to nest the formulas and do the work for you, but that'll take some playing around using COUNTIF, COUNTIFS, IF, and other such statements.
I'll have a play when I get home and see what I can't come up with - but home is presently 4 days away.
You could have a grouping on sheet one that assigns a cost based on any grouping you want (UK local, EU local, the Americas, etc.). Just remember, the more groupings you have, the more complicated the formulas.
The most basic formula I can think of for the setup above and where the aircraft rego is listed on Sheet 2, column A from rows 2 to 10, would be:
=COUNTIF(Sheet2!A2:A10,"G*")
This would count all those registrations that begin with G. You could then use a multiplication formula to multiply that number by whatever value you have assigned to local aircraft, for example:
=B2*0.5
or
=B2*E2
where E2 contains the value you want the number of registrations multiplied by. This allows you to change the value over time, without having to redo all the formulas.
From there... well it depends on what information and groupings you want to see. You can keep using the above formula for every country (that'll get exhausting quick) or you could begin to nest the formulas and do the work for you, but that'll take some playing around using COUNTIF, COUNTIFS, IF, and other such statements.
I'll have a play when I get home and see what I can't come up with - but home is presently 4 days away.
