# Conditional Formula with Multiple Columns - Help!

#### matthewlouis

##### Active Member
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?

 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>

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
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 =AND(B4>B3,C4>C3) =IF(E3,C3,H2) =C3 =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 =AND(B5>B4,C5>C4) =IF(E4,C4,H3) =C4 =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 =AND(B6>B5,C6>C5) =IF(E5,C5,H4) =C5 =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 =AND(B7>B6,C7>C6) =IF(E6,C6,H5) =C6 =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
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
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

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

##### Active Member
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

##### Well-known Member

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
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
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
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:

Replies
1
Views
239
Replies
0
Views
268
Replies
3
Views
334
Replies
8
Views
648
Replies
1
Views
260

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.

### Which adblocker are you using?

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

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