Conditional Formula with Multiple Columns - Help!

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
332
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!

HIGHLOWCLOSE
4/6/20182,656.882,586.272,604.47
4/9/20182,653.552,610.792,613.16
4/10/20182,665.452,635.782,656.87
4/11/20182,661.432,639.252,642.19
4/12/20182,674.722,653.832,663.99
4/13/20182,680.262,645.052,656.30
4/16/20182,686.492,665.162,677.84
4/17/20182,713.342,692.052,706.39
4/18/20182,717.492,703.632,708.64
4/19/20182,702.842,681.902,693.13
4/20/20182,693.942,660.612,670.14
4/23/20182,682.862,657.992,670.29
4/24/20182,683.552,617.322,634.56
4/25/20182,645.302,612.672,639.40
4/26/20182,676.482,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>
 
Last edited by a moderator:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,144
Office Version
  1. 2010
Platform
  1. Windows
These equations should do it for you more or less; I assume yourt data is in columns A to D
A pointB pointCpointA point pricePattern BrokenAtriggeredB TriggeredC triggered
TRUE=IF(E2,C2,H1)=IF(E2,TRUE,AND(J1,NOT(I2)))FALSEFALSE
=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).>
 
Last edited by a moderator:

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,144
Office Version
  1. 2010
Platform
  1. Windows
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:

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
332
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Awesome, offhelip! I will see what I can do with this and get back. Thanks so much for you help!! :)
 
Last edited:

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
332
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

DateHighLowCloseA Point (LOW)B PointC PointA Point PricePattern BrokenA TriggeredB TriggeredC Triggered
4/6/20182,656.88 2,586.27 2,604.47 2,586.27 2,586.27 TRUEFALSEFALSE
4/9/20182,653.55 2,610.79 2,613.16 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/10/20182,665.45 2,635.78 2,656.87 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/11/20182,661.43 2,639.25 2,642.19 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/12/20182,674.72 2,653.83 2,663.99 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/13/20182,680.26 2,645.05 2,656.30 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/16/20182,686.49 2,665.16 2,677.84 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/17/20182,713.34 2,692.05 2,706.39 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/18/20182,717.49 2,703.63 2,708.64 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/19/20182,702.84 2,681.90 2,693.13 TRUEFALSE2,586.27 FALSETRUEFALSEFALSE
4/20/20182,693.94 2,660.61 2,670.14 TRUEFALSE2,586.27 FALSETRUEFALSEFALSE
4/23/20182,682.86 2,657.99 2,670.29 TRUEFALSE2,586.27 FALSETRUEFALSEFALSE
4/24/20182,683.55 2,617.32 2,634.56 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/25/20182,645.30 2,612.67 2,639.40 TRUETRUE2,586.27 FALSETRUEFALSEFALSE
4/26/20182,676.48 2,647.16 2,666.94 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/27/20182,677.35 2,661.45 2,664.02 FALSETRUE2,586.27 FALSETRUEFALSEFALSE

<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

Active Member
Joined
Mar 28, 2014
Messages
332
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
CORRECTION, this is what the table looks like

DateHighLowCloseA Point (LOW)B PointC PointA Point PricePattern BrokenA TriggeredB TriggeredC Triggered
4/6/20182,656.88 2,586.27 2,604.47 2,586.27 2,586.27 TRUEFALSEFALSE
4/9/20182,653.55 2,610.79 2,613.16 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/10/20182,665.45 2,635.78 2,656.87 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/11/20182,661.43 2,639.25 2,642.19 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/12/20182,674.72 2,653.83 2,663.99 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/13/20182,680.26 2,645.05 2,656.30 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/16/20182,686.49 2,665.16 2,677.84 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/17/20182,713.34 2,692.05 2,706.39 FALSETRUE2,586.27 FALSETRUEFALSEFALSE
4/18/20182,717.49 2,703.63 2,708.64 FALSEFALSE2,586.27 FALSETRUEFALSEFALSE
4/19/20182,702.84 2,681.90 2,693.13 TRUEFALSE2,586.27 FALSETRUETRUEFALSE
4/20/20182,693.94 2,660.61 2,670.14 TRUEFALSE2,586.27 FALSETRUETRUEFALSE
4/23/20182,682.86 2,657.99 2,670.29 TRUEFALSE2,586.27 FALSETRUETRUEFALSE
4/24/20182,683.55 2,617.32 2,634.56 FALSEFALSE2,586.27 FALSETRUETRUEFALSE
4/25/20182,645.30 2,612.67 2,639.40 TRUETRUE2,586.27 FALSETRUETRUETRUE
4/26/20182,676.48 2,647.16 2,666.94 FALSETRUE2,586.27 FALSETRUETRUETRUE
4/27/20182,677.35 2,659.01 2,668.92 FALSETRUE2,586.27 FALSETRUETRUETRUE

<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

Well-known Member
Joined
Dec 23, 2017
Messages
2,144
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
332
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
332
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,144
Office Version
  1. 2010
Platform
  1. Windows
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:

Forum statistics

Threads
1,147,743
Messages
5,742,935
Members
423,765
Latest member
PaulD1984

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