Forecast with Vlookup

Hello everyone,

Happy New Year to all. I'm working on a file regarding calculating landing distance. Here is the data for example:

Data:
Landing Weight = 59,400 kg
Pressure altitude = 3,000 ft
Temperature = 30C
Landing configuration = FULL
Brake Mode = Manual
Approach speed = VLS + 8 kt
Slope = -0.07%
Runway Condition = Dry
Both Reversers = Yes

In Cells B2:C10 contains the data mentioned above.
In Cells C22:K32 contains the Corrections on Landing Distance for a Dry runway and similar data for Good runway in Cells C38:K48.
In Cells C36:K36 contains the calculation results based on the data in Cells B2:C20 and Cell G3 where finally Cell M36 displays the final result.

What I'm trying to do is have a formula in Cells E36:K36 that calculates the data from Cells B2:C20 and Cell G3 with the data in Cells C22:K32. So, for example, Cell F36, the result is -660 because since my landing weight is 59,400 which is 6,600kg lower than 66,000kg and as per the rule in Cell F25, for every 1,000kg below 66,000kg, I need to subtract -10 meters which gives me -600.

Another thing I wanted to point out is the Vapp Speed correction. The rule in Cell G5 states for every 5kt, I need to add 60 (cell G28). So looking at Cells B18:C18, you see that I have to take the value that is higher which in this case would be 8. So naturally, one would perform the following math: 2 x 60= 120. That's if I wanted to be conservative but if I were to be specific, I think it would be 1.5 instead of 2, right? Because since I'm 3 knots over 5, and 3 is the half way point of 5 would translate to be .5). So the math could be 1.5 x 60 = 90.

I think a combination of Forecast, Vlookup and Offset may be the solution however, I don't know how to write out the formula. I'm not sure if how I have the data in Cells C22:K32 would be the correct format as I also have 3 different Brake Modes (Manual, AB MED, AB LOW). Guess what I'm trying to do is, based on my Brake Mode (Cell C36) and landing configuration (Cell D36). Cells E36:K36 can calculate the data based off Cells B2:C20 and Cell G3 and display the final result in Cell M36 and Cell M52 which in turn would display the those results in Cell G14 depending on what I select in Cell C8.

Originally Posted by dflak

I made a couple of modifications to your spreadsheet. I gave static names to C2:C9 so that it's easier to debug the formulas.

Also I changed the lookup tables into true Excel Tables called Table_Dry and Table_Good. I made a "composite key" of braking mode and landing configuration. The lookup of the values is based on this composite key using XLOOKUP (it's basically VLOOKUP but you don't have to do it on the first column).

Ok. I see the changes you made. Looks better than mine, LOL. I know the XLOOKUP function is something available in the 365 Office products, which I don't have. I'm using excel 2007/2016, will I be able to construct the table the same way?? If you look in Sheet 2, I just copied the data that was from the aircraft manual.

Originally Posted by dflak

I notice that in your lookup for the the Per 1% Down Slope (cell J35 on my sheet) you have no multiplier should this be multiplied by the actual slope? I assume that the value in cell C7 is the percent slope, so should this value be 20*0.07 or 1.4. Likewise should the formula for an up slope be 0.?

Yes, the value in Cell C7 is in percent (-0.07%) whether down slope (-0.07%) or up slope (0.07%) However, as per the rule in Cell J25, it only applies to down slope. So if the runway is up slope (i.e. 0.07%), no need to make corrections only the opposite. So I would think that a Forecast or probably the Index function ought to work because there are some runways that have slope like 0.70% or 0.30% whether up or down depending which way you are landing, for argument sake, we'll say down (i.e. 0.70% or 0.30%). One can make the conservative argument and say, if slope is -0.70%, yeah round it up to 1% or if -0.30%, keep it at zero and make the calculations. However, if one wanted to be specific, say if slope is -0.70 and at manual brake mode at full configuration Cell J27, instead of 20, it might be something like 13 or if -0.30, instead of 20 could be 8 and anything less than 0.09, no calculation needed. Something like that, I guess.

Originally Posted by dflak

I also have a question about reverse. Should this be all or nothing? In other words, you have 2 reverse or you don't reverse at all. There is no such thing as reversing one prop only.

Each turbo jets has a thrust reverser, i.e. A320 has 2 engines, so 2 reversers. I left out the word Thrust to not make the box look too big. The rule in Cell K25 states, for each reverser working, I need to subtract 20 meters (i.e. landing manual brake mode, full config.). So 2 x -20 = -40 meters. If I only had 1 reverser working then it would be 1 x -20 = -20 meters. So the idea is, if Cell C9 says Yes that both reverser are working, then Cell K35 formula would be 2 x -20 = -40

I guess what I could do is instead of Yes, I can do a dropdown list and select a 2 or 1. Would that be better or can a Yes be a 2 and No be a 1??

Originally Posted by dflak

I also need a better explanation of Vapp. I don't see how you are getting the 5 and 8.

The Vapp is the final approach speed when the Slats/Flaps are in landing configuration and the landing gear are extended. It is based on a winds being reported at the airport. I just threw in a number in Cell C18 so that I can get a formula in Cell G35 to take into account the rule in Cell G25 and the data in Cells G26:31 depending on the braking mode and flap configuration I choose to use. Although, I already have a formula to calculate that from data on a another sheet. Basically Cell C18 would take 1/3 of the headwind component (that's if the headwind was 23, you divide 1/3 of that which gives you 8) in which based on the result, I would then take whichever value is higher from Cell B18 or Cell C18, in this case 8 and apply the rule in Cell G25 & G27. So it should look like this if using manual brake mode with full configuration: 2 x 60 = 120

In other words, how many per 5 kt I have in Cell C18?? 2. Technically 1.5 because my higher value in Cell C18 is 8, not 10. If Cell C18 was 10, then it be 2 x 60 or if Cell C18 was 3, then the value from Cell B18 would apply which then be 1 x 60. Does that make sense??

Originally Posted by dflak

Final question: what type aircraft is this?

All Airbus aircrafts, i.e. A320, A330, etc.


All Cells mentioned are based on your sheet.

Originally Posted by dflak

If you don't have XLOOKUP then we can do it the old fashioned way: Index / Match.

As far as the slope goes, I think we can use it as is (no rounding) with the exception: If(Slope > 0, 0, use the slope calculation) - I think this would be safest.

How about making as: If(Slope > 0.10, 0, use the slope calculation) because I think that even though a full 1% down slope is 20 meters, having a down slope of 0.20 for example can result as 6 meters. I think there is a runway out there that the down slope is 1.20% or something like that but can't think of the airport.

Originally Posted by dflak

Reverse thrust - yes, we can make this 0, 1, 2.

Ok, I can settle for that. I was trying to mimic the same dropdown list layout as in the FlySmart app.

Originally Posted by dflak

Vapp - so you add airspeed depending on headwind? I've had to add airspeed due to gust factor. However, I can see how headwind will reduce landing rollout and I will go with those calculations. What I don't see is where headwind is added as one of the variables or why you would need two cells to enter it. So, I am still not getting this.

Just take the values in Cells B18:C18 as face value for now. As mentioned, I just threw in a number in Cell C18 because what is important is having a formula in Cell G35 (you sheet) to take into account the rule in Cell G25, the data in Cells G26:31 depending on the braking mode and flap configuration I choose to use. How I am coming up with a value in Cell C18, as mentioned, I have another file where I input the headwind, Cell C18 has the formula that will show the final result in Cell C18. Again, don't worry about how I am getting the values in Cells C18, its just a random number I input there while Cell B18 is a fixed number.

If it still bugs you, LOL, the link I sent you as a private message, the pdf. file shows how the Vapp speed is calculated but again, don't worry about the formula. I already have it. Just focus on the formula I need in Cell G35.

Originally Posted by dflak

P.S. As you can see, I'm an ex-C-130 jock.

Didn't know that, LOL. As you see, I'm an Airbus jock.

Originally Posted by dflak

I am getting a much better handle on this going through link you mentioned.

Glad the file in the link I sent you helped you get a better picture.

Originally Posted by dflak

I am looking at the tables you have on sheet 3 and I notice that Column F doesn't match what you have for that column. It compensates for weights above 66T you have a figure for below 66T.

Yes, I did that intentionally. If you look at Sheet 2, the tables look exactly how they are from the aircraft manual. However, I changed Column F data with that of which is outlined in Cell B15. I did it this way to make it easier since I wouldn't be landing an aircraft above 66,000 kgs. As you stroll down the sheet, you see other tables that display the data depending on the runway condition and you also see that at the bottom of each table, there is a note "weight correction."

Originally Posted by dflak

I am working on a way to use all 6 charts - Dry to Poor without having to duplicate the tables.

If you think you can do that, try it out. But remember, in each table at the bottom of them, there are different weight corrections rules. For example, Medium to Poor says if CONF FULL, subtract 20m per 1T below 66T. If CONF 3, subtract 30m per 1T below 66T while for Medium, it states subtract 20m per 1T below 66T, regardless is I use configuration 3 or full.

Originally Posted by dflak

Let me know what you think.

I say keep it simple.

Attached is a update to my file. Take a look at Cells C36:K36 for example. You can see the formulas, some any way, that I have going. I just have a little problem with Cell G36:H36 and Cell J36 in terms of coming up with the formula I need. I think the Forecast and Offset function are the ones that are going to work but not sure.