INDEX and MATCH with Multiple Conditions

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This is a spreadsheet for creating historical probabilities for stock trading. I have successfully used this system for many years -- but I have never codified it in Excel, just used stock charts with anecdotal conclusions from gains and losses. Stocks consistently break higher (and lower) only to come back and “test” important prices that were broken on the way up (or way down). These “tests” occur at prices called Swing Point Highs (SPHs) and Swing Point Lows (SPLs). This sheet is for SPHs and I’ll just reverse the formulas for the SPL sheet.

This is an Index and Match with multiple conditions spreadsheet. I don’t need formulas for columns H through O (hat tip to PJMorris for solving those for me and Jasonb75’s help!) I need formulas for Columns P through U. There are no formulas in the spreadsheet below posted using XLB22; just the actual results that should be returned with the right formula.

I show THREE examples that are color-coded to match. There may be a way to decrease the number of columns (?) but I would rather have the columns as presented for analysis.

Column P and Column Q: This is where I want to see when (if) the price of the Swing Point High (SPH) in Column H was touched (tested) <= 6 trading days using the date from Column O. You index Column D for that result in Column P.

For example, in the 1st one color-coded YELLOW, the Swing Point High of 2,187.87 in Column H, Row 4 was NOT tested, so there is “No” there. In other words, between the date the SPH of 2,187.87 was closed over on 11/21/16 (Column M), 6 trading days from 11/21/16 is . . . 11/30/16. There wasn’t a price that touched the SPH of 2,187.87 between 11/22/16 (day after 11/21/16) and 11/30/16 (Column D) that touched (tested) the 2,187.87 SPH. Thus, all tests that occurred <= 6 days will be in Column P with the corresponding date in Column Q.

Column R and Column S: This is where the 1ST test occurred > 6 days, i.e., 7 days or more. In this 1st example in YELLOW, the 1st test came on the 60th trading day (12/1/16) at 2,187.44. (2187.44 is less than 2,187.87, that’s a test). IMPORTANT: There’s a 60-day window where tests must occur between or they aren’t relevant. In this case, the 60-day window ended on 12/1/2016 which just happened to be the date of the 1st test. Had the test come on 12/2/2016, there would be a “No” in Column R, Row 4 and Column S, Row 4.

Column T and Column U: What happens with stocks is after they test the SPH the 1st time, the price will bounce . . . then they may (or may not) come back for a 2nd test, bounce from there (Column T). Then, then they may come back and test a 3rd time (Column U). Finally, note that the SIX-day window used on the 1st test does NOT apply to the 2nd and 3rd tests. However, the SIXTY-day window does apply to the 2nd and 3rd tests – in this case 12/1/16 – so the 2nd and 3rd tests must occur on or before 12/1/16, in this example, it wasn’t applicable.

2020 July 11 Data for PJ.xlsb
ABCDEFGHIJKLMNOPQRSTU
1ABCDEFGHIJKLMNOPQRSTU
2Daily SPHDateDate IndexHighLowCloseDaily VolumeSwing Point High PriceDate of Swing Point HighSwing Point High Volume60 Day Window for "Closed Over" DatePrice Closed Over Swing Point HighDate Price Closed Over Swing Point HighVolume on Date Price Closed Over Swing Point High6 - Day Window for FIRST Test DateFIRST TEST - Index Column D to see if price touched the Swing Point High Price <= Column O Date (6 Days)First Test Date <= 6 BarsIndex Column D - did Price FIRST Test into Swing Point High Price GREATER THAN Column O Date AND LESS THAN Column K DateFirst Test Date > 6 BarsIndex Column D - did Price SECOND Test into Swing Point High Price LESS THAN Column K DateIndex Column D - did Price THIRD Test into Swing Point High Price LESS THAN Column K Date
3
4Daily SPH9/7/16-9652,187.872,179.072,186.163,319,420,0002,187.879/7/20163,319,420,00012/1/20162,198.18 11/21/20163,607,010,00011/30/2016NoNo2,187.4412/1/2016NoNo
5Daily SPH9/8/16-9642,184.942,177.492,181.303,727,840,000
6Daily SPH9/9/16-9632,169.082,127.812,127.814,233,960,000
7Daily SPH9/12/16-9622,163.302,119.122,159.044,010,480,000
8Daily SPH9/13/16-9612,150.472,120.272,127.024,141,670,000
9Daily SPH9/14/16-9602,141.332,119.902,125.773,664,100,000
10Daily SPH9/15/16-9592,151.312,122.362,147.263,373,720,000
11Daily SPH9/16/16-9582,146.482,131.202,139.165,014,360,000
12Daily SPH9/19/16-9572,153.612,135.912,139.123,163,000,000
13Daily SPH9/20/16-9562,150.802,139.172,139.763,140,730,000
14Daily SPH9/21/16-9552,165.112,139.572,163.123,712,090,000
15Daily SPH9/22/16-9542,179.992,170.942,177.183,552,830,0002,179.999/22/20163,552,830,00012/16/20162,180.39 11/15/20164,543,860,00011/23/20162,179.2211/16/2016NoNoNoNo
16Daily SPH9/23/16-9532,173.752,163.972,164.693,317,190,000
17Daily SPH9/26/16-9522,158.542,145.042,146.103,216,170,000
18Daily SPH9/27/16-9512,161.132,141.552,159.933,437,770,000
19Daily SPH9/28/16-9502,172.402,151.792,171.373,891,460,000
20Daily SPH9/29/16-9492,172.672,145.202,151.134,249,220,000
21Daily SPH9/30/16-9482,175.302,156.512,168.274,173,340,0002,175.309/30/20164,173,340,00012/27/20162,180.39 11/15/20164,543,860,00011/23/2016NoNoNoNoNoNo
22Daily SPH10/3/16-9472,164.412,154.772,161.203,137,550,000
23Daily SPH10/4/16-9462,165.462,144.012,150.493,750,890,000
24Daily SPH10/5/16-9452,163.952,155.152,159.733,906,550,000
25Daily SPH10/6/16-9442,162.932,150.282,160.773,461,550,000
26Daily SPH10/7/16-9432,165.862,144.852,153.743,619,890,000
27Daily SPH10/10/16-9422,169.602,160.392,163.662,916,550,0002,169.6010/10/20162,916,550,0001/5/20172,180.39 11/15/20164,543,860,00011/23/2016
28Daily SPH10/11/16-9412,161.562,128.842,136.733,438,270,000
29Daily SPH10/12/16-9402,145.362,132.772,139.182,977,100,000
30Daily SPH10/13/16-9392,138.192,114.722,132.553,580,450,000
31Daily SPH10/14/16-9382,149.192,132.982,132.983,228,150,000
32Daily SPH10/17/16-9372,135.612,124.432,126.502,830,390,000
33Daily SPH10/18/16-9362,144.382,135.492,139.603,170,000,000
34Daily SPH10/19/16-9352,148.442,138.152,144.293,362,670,000
35Daily SPH10/20/16-9342,147.182,133.442,141.343,337,170,000
36Daily SPH10/21/16-9332,142.632,130.092,141.163,448,850,000
37Daily SPH10/24/16-9322,154.792,146.912,151.333,357,320,0002,154.7910/24/20163,357,320,0001/20/20172,163.26 11/9/20166,264,150,00011/17/2016
38Daily SPH10/25/16-9312,151.442,141.932,143.163,751,340,000
39Daily SPH10/26/16-9302,145.732,131.592,139.433,775,200,000
40Daily SPH10/27/16-9292,147.132,132.522,133.044,204,830,000
41Daily SPH10/28/16-9282,140.722,119.362,126.414,019,510,000
42Daily SPH10/31/16-9272,133.252,125.532,126.153,922,400,000
43Daily SPH11/1/16-9262,131.452,097.852,111.724,532,160,000
44Daily SPH11/2/16-9252,111.762,094.002,097.944,248,580,000
45Daily SPH11/3/16-9242,102.562,085.232,088.663,886,740,000
46Daily SPH11/4/16-9232,099.072,083.792,085.183,837,860,000
47Daily SPH11/7/16-9222,132.002,100.592,131.523,736,060,000
48Daily SPH11/8/16-9212,146.872,123.562,139.563,916,930,000
49Daily SPH11/9/16-9202,170.102,125.352,163.266,264,150,000
50Daily SPH11/10/16-9192,182.302,151.172,167.486,451,640,000
51Daily SPH11/11/16-9182,165.922,152.492,164.454,988,050,000
52Daily SPH11/14/16-9172,171.362,156.082,164.205,367,200,000
53Daily SPH11/15/16-9162,180.842,166.382,180.394,543,860,000
54Daily SPH11/16/16-9152,179.222,172.202,176.943,830,590,000
55Daily SPH11/17/16-9142,188.062,176.652,187.123,809,160,000
56Daily SPH11/18/16-9132,189.892,180.382,181.903,572,400,000
57Daily SPH11/21/16-9122,198.702,186.432,198.183,607,010,000
58Daily SPH11/22/16-9112,204.802,194.512,202.943,957,940,000
59Daily SPH11/23/16-9102,204.722,194.512,204.723,418,640,000
60Daily SPH11/25/16-9092,213.352,206.272,213.351,584,600,000
61Daily SPH11/28/16-9082,211.142,200.362,201.723,505,650,000
62Daily SPH11/29/16-9072,210.462,198.152,204.663,706,560,000
63Daily SPH11/30/16-9062,214.102,198.812,198.815,533,980,000
64Daily SPH12/1/16-9052,202.602,187.442,191.085,063,740,000
65Daily SPH12/2/16-9042,197.952,188.372,191.953,779,500,000
66Daily SPH12/5/16-9032,209.422,199.972,204.713,895,230,000
67Daily SPH12/6/16-9022,212.782,202.212,212.233,855,320,000
68Daily SPH12/7/16-9012,241.632,208.932,241.354,501,820,000
69Daily SPH12/8/16-9002,251.692,237.572,246.194,200,580,000
70Daily SPH12/9/16-8992,259.802,249.232,259.533,884,480,000
71Daily SPH12/12/16-8982,264.032,252.372,256.964,034,510,000
72Daily SPH12/13/16-8972,277.532,263.322,271.723,857,590,0002,277.5312/13/20163,857,590,0003/13/20172,280.07 1/24/20173,810,960,0002/1/2017
73Daily SPH12/14/16-8962,276.202,248.442,253.284,406,970,000
74Daily SPH12/15/16-8952,272.122,253.772,262.034,168,200,000
75Daily SPH12/16/16-8942,268.052,254.242,258.075,920,340,000
76Daily SPH12/19/16-8932,267.472,258.212,262.533,248,370,000
77Daily SPH12/20/16-8922,272.562,266.142,270.763,298,780,000
78Daily SPH12/21/16-8912,271.232,265.152,265.182,852,230,000
79Daily SPH12/22/16-8902,263.182,256.082,260.962,876,320,000
80Daily SPH12/23/16-8892,263.792,258.842,263.792,020,550,000
81Daily SPH12/27/16-8882,273.822,266.152,268.881,987,080,0002,273.8212/27/20161,987,080,0003/24/20172,276.98 1/6/20173,339,890,0001/17/2017
82Daily SPH12/28/16-8872,271.312,249.112,249.922,392,360,000
83Daily SPH12/29/16-8862,254.512,244.562,249.262,336,370,000
84Daily SPH12/30/16-8852,253.582,233.622,238.832,670,900,000
85Daily SPH1/3/17-8842,263.882,245.132,257.833,770,530,000
86Daily SPH1/4/17-8832,272.822,261.602,270.753,764,890,000
87Daily SPH1/5/17-8822,271.502,260.452,269.003,761,820,000
88Daily SPH1/6/17-8812,282.102,264.062,276.983,339,890,0002,282.101/6/20173,339,890,0004/4/20172,298.37 1/25/20173,846,020,0002/2/2017
89Daily SPH1/9/17-8802,275.492,268.902,268.903,217,610,000
90Daily SPH1/10/17-8792,279.272,265.272,268.903,638,790,000
91Daily SPH1/11/17-8782,275.322,260.832,275.323,620,410,000
92Daily SPH1/12/17-8772,271.782,254.252,270.443,462,130,000
93Daily SPH1/13/17-8762,278.682,271.512,274.643,081,270,000
94Daily SPH1/17/17-8752,272.082,262.812,267.893,584,990,000
95Daily SPH1/18/17-8742,272.012,263.352,271.893,315,250,000
96Daily SPH1/19/17-8732,274.332,258.412,263.693,165,970,000
97Daily SPH1/20/17-8722,276.962,265.012,271.313,524,970,000
98Daily SPH1/23/17-8712,271.782,257.022,265.203,152,710,000
99Daily SPH1/24/17-8702,284.632,266.682,280.073,810,960,000
100Daily SPH1/25/17-8692,299.552,288.882,298.373,846,020,000
101Daily SPH1/26/17-8682,300.992,294.082,296.683,610,360,0002,300.991/26/20173,610,360,0004/24/20172,307.87 2/9/20173,677,940,0002/17/2017
Daily SPH 2016
Cells with Conditional Formatting
CellConditionCell FormatStop If True
T4:V4Cell Value=41229textYES
T4:V4Cell Value=41166textYES
T4:V4Cell Value=41064textYES
T4:V4Cell Value=41229textYES
O4:S4,K4:N965Cell Value=41229textYES
O4:S4,K4:N965Cell Value=41166textYES
O4:S4,K4:N965Cell Value=41064textYES
K4:K965,H4:I965,O4:S4,M4:N965Cell Value=41229textYES
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top