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!

	HIGH	LOW	CLOSE
4/6/2018	2,656.88	2,586.27	2,604.47
4/9/2018	2,653.55	2,610.79	2,613.16
4/10/2018	2,665.45	2,635.78	2,656.87
4/11/2018	2,661.43	2,639.25	2,642.19
4/12/2018	2,674.72	2,653.83	2,663.99
4/13/2018	2,680.26	2,645.05	2,656.30
4/16/2018	2,686.49	2,665.16	2,677.84
4/17/2018	2,713.34	2,692.05	2,706.39
4/18/2018	2,717.49	2,703.63	2,708.64
4/19/2018	2,702.84	2,681.90	2,693.13
4/20/2018	2,693.94	2,660.61	2,670.14
4/23/2018	2,682.86	2,657.99	2,670.29
4/24/2018	2,683.55	2,617.32	2,634.56
4/25/2018	2,645.30	2,612.67	2,639.40
4/26/2018	2,676.48	2,647.16	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.

<tbody>
</tbody>

 

[offthelip]

These equations should do it for you more or less; I assume yourt data is in columns A to D

A point	B point	Cpoint	A point price	Pattern Broken	Atriggered	B Triggered	C triggered
TRUE			=IF(E2,C2,H1)		=IF(E2,TRUE,AND(J1,NOT(I2)))	FALSE	FALSE
	=AND(B3<b2,c3<c2)< td=""></b2,c3<c2)<>	=AND(B4>B3,C4>C3)	=IF(E3,C3,H2)	=C3<h3< td=""></h3<>	=IF(E3,TRUE,AND(J2,NOT(I3)))	=IF(AND(J3,F3),TRUE,AND(K2,NOT(I3)))	=IF(AND(K3,G3),TRUE,AND(L2,NOT(I3)))
	=AND(B4<b3,c4<c3)< td=""></b3,c4<c3)<>	=AND(B5>B4,C5>C4)	=IF(E4,C4,H3)	=C4<h4< td=""></h4<>	=IF(E4,TRUE,AND(J3,NOT(I4)))	=IF(AND(J4,F4),TRUE,AND(K3,NOT(I4)))	=IF(AND(K4,G4),TRUE,AND(L3,NOT(I4)))
	=AND(B5<b4,c5<c4)< td=""></b4,c5<c4)<>	=AND(B6>B5,C6>C5)	=IF(E5,C5,H4)	=C5<h5< td=""></h5<>	=IF(E5,TRUE,AND(J4,NOT(I5)))	=IF(AND(J5,F5),TRUE,AND(K4,NOT(I5)))	=IF(AND(K5,G5),TRUE,AND(L4,NOT(I5)))
	=AND(B6<b5,c6<c5)< td=""></b5,c6<c5)<>	=AND(B7>B6,C7>C6)	=IF(E6,C6,H5)	=C6<h6< td=""></h6<>	=IF(E6,TRUE,AND(J5,NOT(I6)))	=IF(AND(J6,F6),TRUE,AND(K5,NOT(I6)))	=IF(AND(K6,G6),TRUE,AND(L5,NOT(I6)))

<tbody>
</tbody>

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.

Date	High	Low	Close	A Point (LOW)	B Point	C Point	A Point Price	Pattern Broken	A Triggered	B Triggered	C Triggered
4/6/2018	2,656.88	2,586.27	2,604.47	2,586.27			2,586.27		TRUE	FALSE	FALSE
4/9/2018	2,653.55	2,610.79	2,613.16		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/10/2018	2,665.45	2,635.78	2,656.87		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/11/2018	2,661.43	2,639.25	2,642.19		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/12/2018	2,674.72	2,653.83	2,663.99		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/13/2018	2,680.26	2,645.05	2,656.30		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/16/2018	2,686.49	2,665.16	2,677.84		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/17/2018	2,713.34	2,692.05	2,706.39		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/18/2018	2,717.49	2,703.63	2,708.64		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/19/2018	2,702.84	2,681.90	2,693.13		TRUE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/20/2018	2,693.94	2,660.61	2,670.14		TRUE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/23/2018	2,682.86	2,657.99	2,670.29		TRUE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/24/2018	2,683.55	2,617.32	2,634.56		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/25/2018	2,645.30	2,612.67	2,639.40		TRUE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/26/2018	2,676.48	2,647.16	2,666.94		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/27/2018	2,677.35	2,661.45	2,664.02		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE

<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>
</tbody>

 

[matthewlouis]

CORRECTION, this is what the table looks like

Date	High	Low	Close	A Point (LOW)	B Point	C Point	A Point Price	Pattern Broken	A Triggered	B Triggered	C Triggered
4/6/2018	2,656.88	2,586.27	2,604.47	2,586.27			2,586.27		TRUE	FALSE	FALSE
4/9/2018	2,653.55	2,610.79	2,613.16		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/10/2018	2,665.45	2,635.78	2,656.87		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/11/2018	2,661.43	2,639.25	2,642.19		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/12/2018	2,674.72	2,653.83	2,663.99		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/13/2018	2,680.26	2,645.05	2,656.30		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/16/2018	2,686.49	2,665.16	2,677.84		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/17/2018	2,713.34	2,692.05	2,706.39		FALSE	TRUE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/18/2018	2,717.49	2,703.63	2,708.64		FALSE	FALSE	2,586.27	FALSE	TRUE	FALSE	FALSE
4/19/2018	2,702.84	2,681.90	2,693.13		TRUE	FALSE	2,586.27	FALSE	TRUE	TRUE	FALSE
4/20/2018	2,693.94	2,660.61	2,670.14		TRUE	FALSE	2,586.27	FALSE	TRUE	TRUE	FALSE
4/23/2018	2,682.86	2,657.99	2,670.29		TRUE	FALSE	2,586.27	FALSE	TRUE	TRUE	FALSE
4/24/2018	2,683.55	2,617.32	2,634.56		FALSE	FALSE	2,586.27	FALSE	TRUE	TRUE	FALSE
4/25/2018	2,645.30	2,612.67	2,639.40		TRUE	TRUE	2,586.27	FALSE	TRUE	TRUE	TRUE
4/26/2018	2,676.48	2,647.16	2,666.94		FALSE	TRUE	2,586.27	FALSE	TRUE	TRUE	TRUE
4/27/2018	2,677.35	2,659.01	2,668.92		FALSE	TRUE	2,586.27	FALSE	TRUE	TRUE	TRUE

<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>
</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)