Formula or Macro? to answer the following Problem statement

[carling73]

Hey Everyone

I'm trying to work out if single string of excel formula's word be sufficient for the following or whether I'm in the territory of of a Macro/VBA?

I'm trying to solve the following if anyone has any thoughts, suggestions

Problem Statement:- Which specific a) Aircraft is the most suitable for b) a specific route, based on:-

1. c) The flying range of the Aircraft - 1,000km - 18,000km
2. d) The length of the airport runway for both the departure airport and arrival airport - 3,117km - 15,010km
3. e) The runway length needed for any specific aircraft to land? In connection with (d) - 1,565km - 18,000km
4. f) The Stage (Cost) range of the Aircraft - 1 - 12 - meaning if i'm at cost stage 1 only Aircraft with that status should be considered
5. g) The Earnings/Cost Ratio of a specific Aircraft for a specific Route. -$10 - $10

Here's a quick example of the base data being used to to try and answer the above, i'd really appreciate any thoughts/suggestions

Thank You

 

[dermie_72]

a formula way to do this would be to put a concatenator in Column G of the route sheet

=A2&B2

Copy that down
Have a departing airport
arriving airport
lowest runway
Distance
from J2
In lowest runwayuse this formula

=IF(OR(K2="",K3=""),"",XLOOKUP(K2&K3,G:G,E:E,0))

and Distance

=IF(OR(K2="",K3=""),"",XLOOKUP(K2&K3,G:G,F:F,0))

Then in M2 you can put the following formula

=FILTER(Aircraft!A1:A6,(Aircraft!C1:C6<=Route!K4)*(Aircraft!D1:D6>=Route!K5),1)

When you change the Departing and Arriving airports, it will automatically identify the planes you can use in a table beside the data

 

[carling73]

=FILTER(Aircraft!A1:A6,(Aircraft!C1:C6<=Route!K4)*(Aircraft!D1:D6>=Route!K5),1)

@dermie_72 - Thanks so much for your help on this, super appreciated THANK YOU

I've tried to follow what you said, but i'm obviously doing something wrong..... (sorry) - have attached screen shot of what i've done on the Route Tab

I've tried to upload the base speadsheet on google docs if that helps
Template

Book1.xlsx

Aircraft Aircraft Name,Cost Stage,Runwy Req,Range,Speed,J - Pax P/$ 172,1,2500,1000,221,-0.925 340,1,2000,1730,425,-1.244 1900,1,4500,2907,500,-1.351 2000,1,2000,2185,737,0.406 1900C,1,4500,2500,553,-1.264 1900D,1,4500,2776,470,-2.665 2-0-2,1,1565,2900,510,-0.52 208B,1,2990,2000,327,-0.691 328-1...

docs.google.com

Thanks again

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[carling73]

@Fluff - Thanks for the advice appreciated ta Fluff, I've updated as suggested

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to help select/suggest the best aircraft for a specific route
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[carling73]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to help select/suggest the best aircraft for a specific route
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies @Fluff, and thanks again for the instruction, appreciated Thank You!

 

[dermie_72]

@dermie_72 - Thanks so much for your help on this, super appreciated THANK YOU

I've tried to follow what you said, but i'm obviously doing something wrong..... (sorry) - have attached screen shot of what i've done on the Route Tab

I've tried to upload the base speadsheet on google docs if that helps
Template

Book1.xlsx

Aircraft Aircraft Name,Cost Stage,Runwy Req,Range,Speed,J - Pax P/$ 172,1,2500,1000,221,-0.925 340,1,2000,1730,425,-1.244 1900,1,4500,2907,500,-1.351 2000,1,2000,2185,737,0.406 1900C,1,4500,2500,553,-1.264 1900D,1,4500,2776,470,-2.665 2-0-2,1,1565,2900,510,-0.52 208B,1,2990,2000,327,-0.691 328-1...

docs.google.com

Thanks again

In the range that's going from A1:A6, change the a6 to the last row that contains data. Then update the other columns to have the same range. See if that works for you.

 

[carling73]

For some reason it doesn't look like i can use the '=FILTER' formula i'm using 2016 PP

 

[carling73]

Evening All, just wondered if anyone had any other thoughts on this please, i'm still a little stuck, thank you