Finding the value in range by applying the rules

[trader10518]

Hi there!

In A1 we have a current stock price.
In A2 we have a custom integer (let it be 2)
In A3 we have a scanning range.
In column B we have option strike prices.
In columns C and D we have bid and ask prices for each strike.

For each strike price (column B value) I need to scan all other smaller strike prices in range (A3) to find the one which meets the following rules:
1. For each Strike (1) we take its Ask (1) value and scan for another Strike's (2) Bid (2) value, so that ((Strike 1 - Strike 2) / (Ask 1 - Bid 2)) >= A2
2. In column E I want to display the strike which meets the above rule as well as has the smallest MOD(Strike 2 - A1) value.
3. In column F I want to see (Strike 1 - Strike 2) / (Ask 1 - Bid 2) result for the selected strike in column E.
4. If no such value appears in 2 and 3, then "-".

Ideally, it would be updated in real-time if prices change.

Thank you.

TOS_Options.xlsx
	A	B	C	D	E	F
1	1145.45
2	2
3	B5:B90
4		Strike	BID	ASK	Strike 2	Ratio
5		850	0.13	0.17
6		855	0.14	0.26
7		860	0.16	0.27
8		865	0.09	0.29
9		870	0.19	0.22
10		875	0.19	0.25
11		880	0.22	0.31
12		885	0.23	0.26
13		890	0.24	0.29
14		895	0.16	0.3
15		900	0.3	0.33
16		905	0.2	0.34
17		910	0.26	0.42
18		915	0.28	0.38
19		920	0.34	0.41
20		925	0.33	0.45
21		930	0.35	0.47
22		935	0.31	0.5
23		940	0.42	0.54
24		945	0.37	0.6
25		950	0.52	0.59
26		955	0.52	0.64
27		960	0.58	0.69
28		965	0.66	0.76
29		970	0.72	0.8
30		975	0.75	0.87
31		980	0.8	0.96
32		985	0.85	1
33		990	0.89	1.06
34		995	1.1	1.11
35		1000	1.2	1.23
36		1005	1.23	1.41
37		1010	1.45	1.53
38		1015	1.6	1.78
39		1020	1.8	1.9
40		1025	2	2.14
41		1030	2.16	2.37
42		1035	2.44	2.65
43		1040	2.73	2.99
44		1045	3.1	3.3
45		1050	3.55	3.7
46		1055	3.9	4.2
47		1060	4.5	4.7
48		1065	5	5.3
49		1070	5.7	5.85
50		1075	6.25	6.5
51		1080	7.05	7.4
52		1085	8.05	8.3
53		1090	9	9.3
54		1095	10	10.4
55		1100	11.35	11.5
56		1105	12.5	12.9
57		1110	14	14.35
58		1115	15.6	16.05
59		1120	17.3	17.7
60		1125	19.1	19.55
61		1130	21.1	21.75
62		1135	23.25	23.95
63		1140	25.6	26.15
64		1145	28.1	28.85
65		1150	30.75	31.5
66		1155	33.55	34.4
67		1160	36.5	37.4
68		1165	39.6	40.5
69		1170	42.8	43.7
70		1175	46.15	47.25
71		1180	49.6	50.75
72		1190	56.85	58.1
73		1200	64.55	66
74		1210	72.5	74.15
75		1220	80.95	82.6
76		1230	89.5	91.3
77		1240	98.35	100.25
78		1250	107.7	109.4
79		1260	116.6	118.9
80		1270	126.05	128.35
81		1275	130.8	133.15
82		1280	135.55	137.95
83		1290	145.3	147.45
84		1300	155.15	157.3
85		1325	179.3	181.8
86		1350	204	206.5
87		1375	228.75	231.3
88		1400	253.65	256.2
89		1425	278.55	281.1
90		1450	303.5	306.05
Sheet1 (2)