Conditional Formula with Multiple Columns - Help!

matthewlouis

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
374
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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]
 
Last edited by a moderator:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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).>
 
Last edited by a moderator:
Upvote 0
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)<>
 
Last edited by a moderator:
Upvote 0
Awesome, offhelip! I will see what I can do with this and get back. Thanks so much for you help!! :)
 
Last edited:
Upvote 0
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>
 
Upvote 0
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>
 
Upvote 0
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<>
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
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 (?)
 
Last edited:
Upvote 0
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)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Index Match Multiple Columns - Exact and Approximate
Replies
4
Views
1K
Fluff
Fluff
A
  • Question Question
Replacing/updating data in a running column with previously unused data in rows above
Replies
1
Views
609
aiki100
A
L
  • Question Question
Need help figuring out a formula to calculate prices based on 4 conditions
Replies
1
Views
1K
Legacy 483325
L
matthewlouis
  • Question Question
INDEX and MATCH with Multiple Conditions
Replies
0
Views
544
matthewlouis
matthewlouis
K
  • Question Question
CountIfs formula with multiple conditions
Replies
3
Views
596
karen2712
K

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

Share this page

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
Back
Top