Forecast formula

[wxman16]

Hello everyone,

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:D20 with the data in Cells C22:K32. If you take a look at Cells C36:K36 for example, you can see some of the formulas 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.

Basically, based on the result in Cell B18 and C18, I would take whichever value is higher, 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. Technically 1.5 (1.5 x 60 = 90) 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. In other words, how many per 5 kt do I have in Cell C18, 2 or 1.5??

Landing distance.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		LDG weight	59400		Weight Correction		6600	kgs
3		elevation	3000				-660	meters
4		Temp	30
5		LDG CONF	Full
6		Brake mode	Manual				Safety Factor
7		Slope	-0.7				1.15
8		RWY COND	Dry
9		Both Rev working	Yes
10
11
12		Vapp Correction
13		5 KT	1/3 headwind (excluding gust) max = 15 kt				Required Landing Distance
14							808.45
15
16
17		Whichever is Higher
18		5	8
19
20		ISA DEV	21	2.1		63
21
22			Dry
23			Corrections on Landing Distance			Weight	Vapp SPEED	Altitude	Temp	Slope	Reversers
24
25			Brake Mode	LDG CONF	Ref Dist (m) for 66T	Per 1T below 66T	Per 5kt	Per 1000ft above SL	Per 10°C above ISA	Per 1% Down Slope	Per Reverser Working		-20
26
27			Manual	Conf 3	1130	-10	80	50	40	20	-20
28			Manual	Full	1080	-10	60	40	30	20	-20
29			AB MED	Conf 3	1420	-10	100	50	50	10	0
30			AB MED	Full	1360	-10	90	50	40	10	0
31			AB LOW	Conf 3	2030	-10	150	80	70	30	-10
32			AB LOW	Full	1930	-10	130	80	60	20	-10
33
34			Braking Mode	LDG CONF	Ref Dist (m) for 66T	Per 1T below 66T	Per 5kt	Per 1000ft above SL	Per 10°C above ISA	Per 1% Down Slope	Per Thrust Reverser Ops		Final
35													result
36			Manual	Full	1080	-660	120	120	63	20	-40		808.45
37
38			Good
39			Corrections on Landing Distance			Weight	Vapp SPEED	Altitude	Temp	Slope	Reversers
40
41			Brake Mode	LDG CONF	Ref Dist (m) for 66T	Per 1T below 66T	Per 5kt	Per 1000ft above SL	Per 10°C above ISA	Per 1% Down Slope	Per Reverser Working
42
43			Manual	Conf 3	1480	-20	120	80	70	60	-60
44			Manual	Full	1370	-10	100	70	60	40	-50
45			AB MED	Conf 3	1540	-20	120	80	70	60	-30
46			AB MED	Full	1420	-10	110	70	60	40	-10
47			AB LOW	Conf 3	2030	-20	150	80	70	30	-10
48			AB LOW	Full	1930	-10	130	80	60	20	-10
49
50			Braking Mode	LDG CONF	Ref Dist (m) for 66T	Per 1T below 66T	Per 5kt	Per 1000ft above SL	Per 10°C above ISA	Per 1% Down Slope	Per Thrust Reverser Ops		Final
51													result
52			Manual	Full	1370	-660	100	210	126	40	-100		1248.9
53
54
55
Sheet3

Cell Formulas
Range	Formula
G2	G2	=66000-C2
G3	G3	=G2*F27/100
G14	G14	=M36
C20	C20	=C4-(15-2*(C3/1000))
D20	D20	=C20/10
F20	F20	=INDEX(I27:I32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*D20
M25	M25	=INDEX(K27:K32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))
C36	C36	=C6
D36	D36	=C5
E36	E36	=INDEX(E27:E32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))
F36	F36	=INDEX(F27:F32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*G2/100
H36,H52	H36	=H28*3
I36	I36	=INDEX(I27:I32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*D20
K36	K36	=IF(C9="Yes",M25*2,IF(C9="No",M25*1,0))
M36	M36	=SUM(E36:K36)*G7
C52	C52	=C6
D52	D52	=C5
E52	E52	=INDEX(E43:E48,MATCH(1,INDEX((C52=C43:C48)*(D52=D27:D32),0,1),0))
F52	F52	=INDEX(F43:F48,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*G2/100
G52	G52	=G44
I52	I52	=INDEX(I43:I48,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*D20
K52	K52	=K44*2
M52	M52	=SUM(E52:K52)*G7

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=$P$2:$P$4
C6	List	=$O$2:$O$5
C8	List	=$Q$2:$Q$4
C9	List	=$R$2:$R$4

 

[BigBeachBananas]

I would take whichever value is higher, in this case 8 and apply the rule in Cell G25 & G27

Cell G25 is a header. What is the rule?

So it should look like this if using manual brake mode with full configuration: 2 x 60 = 120. Technically 1.5 (1.5 x 60 = 90) 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. In other words, how many per 5 kt do I have in Cell C18, 2 or 1.5??

I don't understand your calculation of how is 8=> 1.5, 10=>2, and 5=>1?

 

[Peter_SSs]

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: Forecast with Vlookup
If you have posted the question at more 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.

 

[wxman16]

Cell G25 is a header. What is the rule?

I don't understand your calculation of how is 8=> 1.5, 10=>2, and 5=>1?

Cell G25 refers to the Vapp Speed correction values in Cells B18 & C18. The rule in Cell G25 states for every 5kt based on the higher value of the two in Cells B18 or C18, I would have to multiply it by the value in Cells G27:G32 depending on the brake mode and landing configuration selected. 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 if being conservative: 2 x 60= 120. If Cell C18 were 3, then I would take the value in B18 instead and perform the math: 1 x 60 = 60.

How I got 1.5??? To be more specific with the calculation, since Cell C18 has the higher value, I need to know how many times I need to multiply how many per 5 kt I have with the value in Cell G28. In this case, you would divide 8 by 5 (8/5 = 1.6). So the result I should have displayed in Cell G36 should be 96 because the math was 1.6 x 60 = 96

Simply math I think, right?? Say, for every 3 apples, you need to count them as 1 and multiply that value by 5. So, if you have 11 apples for example, what would the value be when you multiply that value by 5?? 11/3 = 3.7 then 3.7 x 5 = 18.5

Hope that made sense.

 

[BigBeachBananas]

If I understand you correctly, this is what you're looking for:

Book1.xlsx
	G
27	96
Sheet3

Cell Formulas
Range	Formula
G27	G27	=MAX(B18:C18)/5*60

 

[wxman16]

If I understand you correctly, this is what you're looking for:

Book1.xlsx
	G
27	96
Sheet3

Cell Formulas
Range	Formula
G27	G27	=MAX(B18:C18)/5*60

Yes, thank you. The MAX function was what I was looking for . The formula below, I have in Cell G36 which is what I needed. I figured it out , brain fart, :

=INDEX(G27:G32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*MAX(B18:C18)/5


How can I clean up the formula in Cells I36:K36 to look a bit more cleaner?? For example, in Cell I36, I have the following formula:

I36=INDEX(I27:I32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*D20

The 2 formulas below, how am I able to combine them and then add it to the formula above (taking out the *D20) and replacing it with a combine formula from the below formulas??
D20=C20/10
C20=C4-(15-2*(C3/1000))

I had this going but it was giving me the wrong result, giving me 29.1 which should be 2.1: C20=C4-(15-2*(C3/1000))/10

Cells J36 & K36, I'm still trying to find a better formula

 

[BigBeachBananas]

Book1.xlsx
	I
36	63
Sheet3

Cell Formulas
Range	Formula
I36	I36	=INDEX(I27:I32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*(500*(C4-15)+C3)/5000

 

[wxman16]

Book1.xlsx
	I
36	63
Sheet3

Cell Formulas
Range	Formula
I36	I36	=INDEX(I27:I32,MATCH(1,INDEX((C36=C43:C48)*(D36=D27:D32),0,1),0))*(500*(C4-15)+C3)/5000

Thanks.