Conditional Formula with Multiple Columns - Help!

[matthewlouis]

I have 3 columns of stock prices – the date, the low, the high, and the close. I am trying to build a formula (may have to be in multiple columns with each column having its own formula) that returns the A point price, B point price, and C point price of something called the ABCD pattern -- D is the calculated projection of price based on the price of A, B, and C. Very accurate and time tested way to project prices. I need to an algoto calculate this everyday without having to look at a chart.

A bullish pattern is when the price starts off at a low point – Point A of the ABCD pattern. As price rises, the B point is formed once you get a lower high and lower low. Then when you get a higher high and higher low, the price previous to that bar is the C point. This may be multiples formulas (I think) that will identify the A, B, and C points given the higher highs, higher lows, lower highs, and lower lows. It has to be looked day-by-day to see if any of these A, B, or C price points have been created.

NOTE: The pattern is broken IF the price forms the ABC part of the pattern and then price CLOSES below A. That’s a failed pattern.

I don’t know how to do VBA – which I think would work best – so can anyone help me do this in columns with formulas for each column?

Thanks in advance!

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl110"][/TD]
[TD="class: cms_table_xl101"]HIGH[/TD]
[TD="class: cms_table_xl104, width: 134"]LOW[/TD]
[TD="class: cms_table_xl111, width: 164"]CLOSE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/6/2018[/TD]
[TD="class: cms_table_xl101"]2,656.88[/TD]
[TD="class: cms_table_xl104"]2,586.27[/TD]
[TD="class: cms_table_xl111"]2,604.47[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/9/2018[/TD]
[TD="class: cms_table_xl101"]2,653.55[/TD]
[TD="class: cms_table_xl104"]2,610.79[/TD]
[TD="class: cms_table_xl111"]2,613.16[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/10/2018[/TD]
[TD="class: cms_table_xl101"]2,665.45[/TD]
[TD="class: cms_table_xl104"]2,635.78[/TD]
[TD="class: cms_table_xl111"]2,656.87[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/11/2018[/TD]
[TD="class: cms_table_xl101"]2,661.43[/TD]
[TD="class: cms_table_xl104"]2,639.25[/TD]
[TD="class: cms_table_xl111"]2,642.19[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/12/2018[/TD]
[TD="class: cms_table_xl101"]2,674.72[/TD]
[TD="class: cms_table_xl104"]2,653.83[/TD]
[TD="class: cms_table_xl111"]2,663.99[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/13/2018[/TD]
[TD="class: cms_table_xl101"]2,680.26[/TD]
[TD="class: cms_table_xl104"]2,645.05[/TD]
[TD="class: cms_table_xl111"]2,656.30[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/16/2018[/TD]
[TD="class: cms_table_xl101"]2,686.49[/TD]
[TD="class: cms_table_xl104"]2,665.16[/TD]
[TD="class: cms_table_xl111"]2,677.84[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/17/2018[/TD]
[TD="class: cms_table_xl101"]2,713.34[/TD]
[TD="class: cms_table_xl104"]2,692.05[/TD]
[TD="class: cms_table_xl111"]2,706.39[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/18/2018[/TD]
[TD="class: cms_table_xl101"]2,717.49[/TD]
[TD="class: cms_table_xl104"]2,703.63[/TD]
[TD="class: cms_table_xl111"]2,708.64[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/19/2018[/TD]
[TD="class: cms_table_xl101"]2,702.84[/TD]
[TD="class: cms_table_xl104"]2,681.90[/TD]
[TD="class: cms_table_xl111"]2,693.13[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/20/2018[/TD]
[TD="class: cms_table_xl101"]2,693.94[/TD]
[TD="class: cms_table_xl104"]2,660.61[/TD]
[TD="class: cms_table_xl111"]2,670.14[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/23/2018[/TD]
[TD="class: cms_table_xl101"]2,682.86[/TD]
[TD="class: cms_table_xl104"]2,657.99[/TD]
[TD="class: cms_table_xl111"]2,670.29[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl108, align: right"]4/24/2018[/TD]
[TD="class: cms_table_xl100"]2,683.55[/TD]
[TD="class: cms_table_xl103"]2,617.32[/TD]
[TD="class: cms_table_xl113"]2,634.56[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/25/2018[/TD]
[TD="class: cms_table_xl101"]2,645.30[/TD]
[TD="class: cms_table_xl104"]2,612.67[/TD]
[TD="class: cms_table_xl111"]2,639.40[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl110, align: right"]4/26/2018[/TD]
[TD="class: cms_table_xl101"]2,676.48[/TD]
[TD="class: cms_table_xl104"]2,647.16
[/TD]
[TD="class: cms_table_xl111"]2,666.95


The A point is the low on April 6 of 2656.88.

The B point is April 18 at 2717.49 because April 19 had a lower low and lower high than APril18.

The C point low is April 25 at 2612.67 because April 26 had a higher high and a higher low than April 25.



[/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

These equations should do it for you more or less; I assume yourt data is in columns A to D
[TABLE="width: 1462"]
<tbody>[TR]
[TD]A point[/TD]
[TD]B point[/TD]
[TD]Cpoint[/TD]
[TD]A point price[/TD]
[TD]Pattern Broken[/TD]
[TD]Atriggered[/TD]
[TD]B Triggered[/TD]
[TD]C triggered[/TD]
[/TR]
[TR]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(E2,C2,H1)[/TD]
[TD][/TD]
[TD]=IF(E2,TRUE,AND(J1,NOT(I2)))[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=AND(B3<b2,c3<c2)< td=""></b2,c3<c2)<>[/TD]
[TD]=AND(B4>B3,C4>C3)[/TD]
[TD]=IF(E3,C3,H2)[/TD]
[TD]=C3<h3< td=""></h3<>[/TD]
[TD]=IF(E3,TRUE,AND(J2,NOT(I3)))[/TD]
[TD]=IF(AND(J3,F3),TRUE,AND(K2,NOT(I3)))[/TD]
[TD]=IF(AND(K3,G3),TRUE,AND(L2,NOT(I3)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=AND(B4<b3,c4<c3)< td=""></b3,c4<c3)<>[/TD]
[TD]=AND(B5>B4,C5>C4)[/TD]
[TD]=IF(E4,C4,H3)[/TD]
[TD]=C4<h4< td=""></h4<>[/TD]
[TD]=IF(E4,TRUE,AND(J3,NOT(I4)))[/TD]
[TD]=IF(AND(J4,F4),TRUE,AND(K3,NOT(I4)))[/TD]
[TD]=IF(AND(K4,G4),TRUE,AND(L3,NOT(I4)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=AND(B5<b4,c5<c4)< td=""></b4,c5<c4)<>[/TD]
[TD]=AND(B6>B5,C6>C5)[/TD]
[TD]=IF(E5,C5,H4)[/TD]
[TD]=C5<h5< td=""></h5<>[/TD]
[TD]=IF(E5,TRUE,AND(J4,NOT(I5)))[/TD]
[TD]=IF(AND(J5,F5),TRUE,AND(K4,NOT(I5)))[/TD]
[TD]=IF(AND(K5,G5),TRUE,AND(L4,NOT(I5)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=AND(B6<b5,c6<c5)< td=""></b5,c6<c5)<>[/TD]
[TD]=AND(B7>B6,C7>C6)[/TD]
[TD]=IF(E6,C6,H5)[/TD]
[TD]=C6<h6< td=""></h6<>[/TD]
[TD]=IF(E6,TRUE,AND(J5,NOT(I6)))[/TD]
[TD]=IF(AND(J6,F6),TRUE,AND(K5,NOT(I6)))[/TD]
[TD]=IF(AND(K6,G6),TRUE,AND(L5,NOT(I6)))[/TD]
[/TR]
</tbody>[/TABLE]

Note you haven't defined how you are going to detect the point A so I have just written it as TRUE!!
The B column equation should be and(b3<b2,c3<c2).<b2,c3<c2). sorry="" about="" thast<b2,c3<c2)
<b2,c3<c2)



<b2,c3<c2)< html=""></b2,c3<c2)<></b2,c3<c2)
</b2,c3<c2).>

 

[offthelip]

For some reason the website wouldn't let me correct the equation for B point
it should be;
=a n d(b3 < b2 , c3 < c2 )
Also the patter broken equation should be
= C 3 < H 3
A point is in Column E , <b2,c3<c2)< html=""></b2,c3<c2)<>

 

[matthewlouis]

Awesome, offhelip! I will see what I can do with this and get back. Thanks so much for you help!!

 

[matthewlouis]

OK, maybe I am missing something . . . but I input your formulas and this is what it looks like.

<colgroup><col style="mso-width-source:userset;mso-width-alt:5600;width:131pt" width="175"> <col style="mso-width-source:userset;mso-width-alt:4384;width:103pt" width="137"> <col style="mso-width-source:userset;mso-width-alt:4288;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:5312; width:125pt" span="9" width="166"> </colgroup><tbody>
[TD="class: xl99, width: 175"]Date[/TD]
[TD="class: xl99, width: 137"]High[/TD]
[TD="class: xl99, width: 134"]Low[/TD]
[TD="class: xl99, width: 166"]Close[/TD]
[TD="class: xl99, width: 166"]A Point (LOW)[/TD]
[TD="class: xl99, width: 166"]B Point[/TD]
[TD="class: xl99, width: 166"]C Point[/TD]
[TD="class: xl99, width: 166"]A Point Price[/TD]
[TD="class: xl99, width: 166"]Pattern Broken[/TD]
[TD="class: xl99, width: 166"]A Triggered[/TD]
[TD="class: xl99, width: 166"]B Triggered[/TD]
[TD="class: xl99, width: 166"]C Triggered[/TD]

[TD="class: xl100"]4/6/2018[/TD]
[TD="class: xl101"]2,656.88 [/TD]
[TD="class: xl102"]2,586.27 [/TD]
[TD="class: xl103"]2,604.47 [/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/9/2018[/TD]
[TD="class: xl101"]2,653.55 [/TD]
[TD="class: xl102"]2,610.79 [/TD]
[TD="class: xl105"]2,613.16 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/10/2018[/TD]
[TD="class: xl101"]2,665.45 [/TD]
[TD="class: xl102"]2,635.78 [/TD]
[TD="class: xl106"]2,656.87 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/11/2018[/TD]
[TD="class: xl101"]2,661.43 [/TD]
[TD="class: xl102"]2,639.25 [/TD]
[TD="class: xl106"]2,642.19 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/12/2018[/TD]
[TD="class: xl101"]2,674.72 [/TD]
[TD="class: xl102"]2,653.83 [/TD]
[TD="class: xl106"]2,663.99 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/13/2018[/TD]
[TD="class: xl101"]2,680.26 [/TD]
[TD="class: xl102"]2,645.05 [/TD]
[TD="class: xl106"]2,656.30 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/16/2018[/TD]
[TD="class: xl101"]2,686.49 [/TD]
[TD="class: xl102"]2,665.16 [/TD]
[TD="class: xl106"]2,677.84 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/17/2018[/TD]
[TD="class: xl101"]2,713.34 [/TD]
[TD="class: xl102"]2,692.05 [/TD]
[TD="class: xl106"]2,706.39 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/18/2018[/TD]
[TD="class: xl101"]2,717.49 [/TD]
[TD="class: xl102"]2,703.63 [/TD]
[TD="class: xl106"]2,708.64 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/19/2018[/TD]
[TD="class: xl101"]2,702.84 [/TD]
[TD="class: xl102"]2,681.90 [/TD]
[TD="class: xl106"]2,693.13 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/20/2018[/TD]
[TD="class: xl101"]2,693.94 [/TD]
[TD="class: xl102"]2,660.61 [/TD]
[TD="class: xl106"]2,670.14 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/23/2018[/TD]
[TD="class: xl101"]2,682.86 [/TD]
[TD="class: xl102"]2,657.99 [/TD]
[TD="class: xl106"]2,670.29 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/24/2018[/TD]
[TD="class: xl101"]2,683.55 [/TD]
[TD="class: xl102"]2,617.32 [/TD]
[TD="class: xl106"]2,634.56 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/25/2018[/TD]
[TD="class: xl101"]2,645.30 [/TD]
[TD="class: xl102"]2,612.67 [/TD]
[TD="class: xl106"]2,639.40 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/26/2018[/TD]
[TD="class: xl101"]2,676.48 [/TD]
[TD="class: xl102"]2,647.16 [/TD]
[TD="class: xl106"]2,666.94 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/27/2018[/TD]
[TD="class: xl101"]2,677.35 [/TD]
[TD="class: xl102"]2,661.45 [/TD]
[TD="class: xl106"]2,664.02 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

</tbody>

 

[matthewlouis]

CORRECTION, this is what the table looks like

<colgroup><col style="mso-width-source:userset;mso-width-alt:5600;width:131pt" width="175"> <col style="mso-width-source:userset;mso-width-alt:4384;width:103pt" width="137"> <col style="mso-width-source:userset;mso-width-alt:4288;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:5312; width:125pt" span="9" width="166"> </colgroup><tbody>
[TD="class: xl99, width: 175"]Date[/TD]
[TD="class: xl99, width: 137"]High[/TD]
[TD="class: xl99, width: 134"]Low[/TD]
[TD="class: xl99, width: 166"]Close[/TD]
[TD="class: xl99, width: 166"]A Point (LOW)[/TD]
[TD="class: xl99, width: 166"]B Point[/TD]
[TD="class: xl99, width: 166"]C Point[/TD]
[TD="class: xl99, width: 166"]A Point Price[/TD]
[TD="class: xl99, width: 166"]Pattern Broken[/TD]
[TD="class: xl99, width: 166"]A Triggered[/TD]
[TD="class: xl99, width: 166"]B Triggered[/TD]
[TD="class: xl99, width: 166"]C Triggered[/TD]

[TD="class: xl100"]4/6/2018[/TD]
[TD="class: xl101"]2,656.88 [/TD]
[TD="class: xl102"]2,586.27 [/TD]
[TD="class: xl103"]2,604.47 [/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/9/2018[/TD]
[TD="class: xl101"]2,653.55 [/TD]
[TD="class: xl102"]2,610.79 [/TD]
[TD="class: xl105"]2,613.16 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/10/2018[/TD]
[TD="class: xl101"]2,665.45 [/TD]
[TD="class: xl102"]2,635.78 [/TD]
[TD="class: xl106"]2,656.87 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/11/2018[/TD]
[TD="class: xl101"]2,661.43 [/TD]
[TD="class: xl102"]2,639.25 [/TD]
[TD="class: xl106"]2,642.19 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/12/2018[/TD]
[TD="class: xl101"]2,674.72 [/TD]
[TD="class: xl102"]2,653.83 [/TD]
[TD="class: xl106"]2,663.99 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/13/2018[/TD]
[TD="class: xl101"]2,680.26 [/TD]
[TD="class: xl102"]2,645.05 [/TD]
[TD="class: xl106"]2,656.30 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/16/2018[/TD]
[TD="class: xl101"]2,686.49 [/TD]
[TD="class: xl102"]2,665.16 [/TD]
[TD="class: xl106"]2,677.84 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/17/2018[/TD]
[TD="class: xl101"]2,713.34 [/TD]
[TD="class: xl102"]2,692.05 [/TD]
[TD="class: xl106"]2,706.39 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/18/2018[/TD]
[TD="class: xl101"]2,717.49 [/TD]
[TD="class: xl102"]2,703.63 [/TD]
[TD="class: xl106"]2,708.64 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/19/2018[/TD]
[TD="class: xl101"]2,702.84 [/TD]
[TD="class: xl102"]2,681.90 [/TD]
[TD="class: xl106"]2,693.13 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/20/2018[/TD]
[TD="class: xl101"]2,693.94 [/TD]
[TD="class: xl102"]2,660.61 [/TD]
[TD="class: xl106"]2,670.14 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/23/2018[/TD]
[TD="class: xl101"]2,682.86 [/TD]
[TD="class: xl102"]2,657.99 [/TD]
[TD="class: xl106"]2,670.29 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/24/2018[/TD]
[TD="class: xl101"]2,683.55 [/TD]
[TD="class: xl102"]2,617.32 [/TD]
[TD="class: xl106"]2,634.56 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]FALSE[/TD]

[TD="class: xl100"]4/25/2018[/TD]
[TD="class: xl101"]2,645.30 [/TD]
[TD="class: xl102"]2,612.67 [/TD]
[TD="class: xl106"]2,639.40 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]

[TD="class: xl100"]4/26/2018[/TD]
[TD="class: xl101"]2,676.48 [/TD]
[TD="class: xl102"]2,647.16 [/TD]
[TD="class: xl106"]2,666.94 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]

[TD="class: xl100"]4/27/2018[/TD]
[TD="class: xl101"]2,677.35 [/TD]
[TD="class: xl102"]2,659.01 [/TD]
[TD="class: xl106"]2,668.92 [/TD]
[TD="class: xl104"][/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]2,586.27 [/TD]
[TD="class: xl104"]FALSE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]
[TD="class: xl104"]TRUE[/TD]

</tbody>

 

[offthelip]

I agree , that is what it looks like on my sheet as well. I noticed one error, the pattern borken equation shouild look at the close price not the low price.i.e. D 3 < H 3<h3< html=""></h3<>

 

[matthewlouis]

Yes, OK, made that correction D3 .. . but I don;t understand what it is supposed to look (?) Shouldn't there be prices where the TRUE amd FALSE data is?

 

[matthewlouis]

The Low price where you started with TRUE . . . I just picked that number because the start of an ABCD is with the low. So I need a formula to pick the low spot to start it.

So, if prices were returned for A, B, and C points, then I thought I could do a VLOOKUP or last # in column to get the A, B, and C (?)

 

[offthelip]

I forgot that you wanted the price on the A ,B and C triggers.
The A price is alreadyt there, to get the B and C trigger prices add two extra columns with these equations in them.

Close Price on B trigger ----------------------Close Price on C trigger
=IFERROR(IF(AND(K2,NOT(K1)),D2,M1),0) =IFERROR(IF(AND(L2,NOT(L1)),D2,N1),0)
=IFERROR(IF(AND(K3,NOT(K2)),D3,M2),0) =IFERROR(IF(AND(L3,NOT(L2)),D3,N2),0)
=IFERROR(IF(AND(K4,NOT(K3)),D4,M3),0) =IFERROR(IF(AND(L4,NOT(L3)),D4,N3),0)

Note you haven't said which of the three prices you want, so I have assumed you want the close price (column D)