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]

 

[matthewlouis]

The D is calculated once you have the A, B, and the C. Not sure we are connecting on how an ABCD works (?)

So let's say 2553.80 in the low in this example. I have put all your formulas in place. This is what it says.

<tbody>
[TD="class: xl99"]4/6/2018[/TD]
[TD="class: xl100, width: 137"]2,656.88[/TD]
[TD="class: xl101, width: 134"]2,586.27[/TD]
[TD="class: xl102, width: 166"]2,604.47[/TD]
[TD="class: xl103, width: 166"]2,586.27[/TD]
[TD="class: xl103, width: 166"][/TD]
[TD="class: xl103, width: 166"][/TD]
[TD="class: xl103, width: 166"]2,586.27[/TD]
[TD="class: xl103, width: 166"][/TD]
[TD="class: xl103, width: 166"]TRUE[/TD]
[TD="class: xl103, width: 166"]FALSE[/TD]
[TD="class: xl103, width: 166"]FALSE[/TD]
[TD="class: xl103, width: 166"]0.00[/TD]
[TD="class: xl103, width: 166"]0.00[/TD]

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

[TD="class: xl99"]4/10/2018[/TD]
[TD="class: xl100"]2,665.45[/TD]
[TD="class: xl101"]2,635.78[/TD]
[TD="class: xl105"]2,656.87[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/11/2018[/TD]
[TD="class: xl100"]2,661.43[/TD]
[TD="class: xl101"]2,639.25[/TD]
[TD="class: xl105"]2,642.19[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/12/2018[/TD]
[TD="class: xl100"]2,674.72[/TD]
[TD="class: xl101"]2,653.83[/TD]
[TD="class: xl105"]2,663.99[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/13/2018[/TD]
[TD="class: xl100"]2,680.26[/TD]
[TD="class: xl101"]2,645.05[/TD]
[TD="class: xl105"]2,656.30[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/16/2018[/TD]
[TD="class: xl100"]2,686.49[/TD]
[TD="class: xl101"]2,665.16[/TD]
[TD="class: xl105"]2,677.84[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/17/2018[/TD]
[TD="class: xl100"]2,713.34[/TD]
[TD="class: xl101"]2,692.05[/TD]
[TD="class: xl105"]2,706.39[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/18/2018[/TD]
[TD="class: xl100"]2,717.49[/TD]
[TD="class: xl101"]2,703.63[/TD]
[TD="class: xl105"]2,708.64[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]0.00[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/19/2018[/TD]
[TD="class: xl100"]2,702.84[/TD]
[TD="class: xl101"]2,681.90[/TD]
[TD="class: xl105"]2,693.13[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/20/2018[/TD]
[TD="class: xl100"]2,693.94[/TD]
[TD="class: xl101"]2,660.61[/TD]
[TD="class: xl105"]2,670.14[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/23/2018[/TD]
[TD="class: xl100"]2,682.86[/TD]
[TD="class: xl101"]2,657.99[/TD]
[TD="class: xl105"]2,670.29[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/24/2018[/TD]
[TD="class: xl100"]2,683.55[/TD]
[TD="class: xl101"]2,617.32[/TD]
[TD="class: xl105"]2,634.56[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]0.00[/TD]

[TD="class: xl99"]4/25/2018[/TD]
[TD="class: xl100"]2,645.30[/TD]
[TD="class: xl101"]2,612.67[/TD]
[TD="class: xl105"]2,639.40[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]2,639.40[/TD]

[TD="class: xl99"]4/26/2018[/TD]
[TD="class: xl100"]2,676.48[/TD]
[TD="class: xl101"]2,647.16[/TD]
[TD="class: xl105"]2,666.94[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]2,639.40[/TD]

[TD="class: xl99"]4/27/2018[/TD]
[TD="class: xl100"]2,677.35[/TD]
[TD="class: xl101"]2,659.01[/TD]
[TD="class: xl105"]2,669.91[/TD]
[TD="class: xl103"][/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,586.27[/TD]
[TD="class: xl103"]FALSE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]TRUE[/TD]
[TD="class: xl103"]2,693.13[/TD]
[TD="class: xl103"]2,639.40[/TD]

</tbody>

I can;t follow what this is telling me. The B point is 2717.49 on April 18 -- but I don;t see that anywhere. The C point is 2,612.67 on April 25 but I don;t see that either.

 

[offthelip]

Two reasons:
1st problem

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

You never told me that you wanted the High price for the B point,
To get the high price you need to change the D2 to B2 in the B trigger equations
2:nd PRoblem
You state that the B point is on the 18 April, however if you look at the B trigger it only goes true on the 19th april, This is because the equation is using data that is only available on the 19th. So If you want to use price on the 18th you must realise that you will only know that it is a B trigger a day later.
If you want to do both of these go to the third line down where the equations shows D3 and change this to B2 and copy down , that will give you the figure you are expecting. I would guess that that C trigger is similar but i haven't looked at it.

 

[matthewlouis]

Sorry, I know I didn;t explain it well.

The C Trigger worked. Regarding the B Trigger, is there any way to subract a row to get it? It returns the 2702 as you said it would and the one prior to it is 2717, that is the B Point.

Thanks so much for your patience with me and ALL your help.

 

[matthewlouis]

Hey offthelip! Got the ABCD sheet figured out now. . . could not have done this without your help, thanks so much, you're awesome.

I did an Index and Match to get the B Point High using the last 0.00 as the search and match. Works perfect.

Thanks again !!

 

[offthelip]

The C Trigger worked. Regarding the B Trigger, is there any way to subract a row to get it? It returns the 2702 as you said it would and the one prior to it is 2717, that is the B Point.

You don't need to use index and match to get the B trigger price ( that is a horrible way of doing it) just change the Row number of the value you are picking up for hte B trigger price. i.e on row 11 column M of my workbook I have got:

=IFERROR(IF(AND(K11,NOT(K10)),B10,M10),0)

If you change this to

=IFERROR(IF(AND(K11,NOT(K10)),B9,M10),0)

and then copy this to the whole column you will pick up the previous day's High price for the trigger.
Look through all the equations I have written and try and understand what I have done, this will allow you to modify it to your own requirements. ( Like this modification)

 

[matthewlouis]

Ahhhhhh, OK, I will check that out. Thanks so much, offthelip.

 

[matthewlouis]

YES!!! Works perfect, thanks again.

 

[matthewlouis]

Well . .. running into some that work and some that don't work. Hmmmm. I made the change to
=IFERROR(IF(AND(K11,NOT(K10)),B9,M10),0)

This is the B Triggered formula =IFERROR(IF(AND(K11,G11),TRUE,AND(L10,NOT(J11))),"")

Does that one need to be adjusted?

Again, some of the highs are correct and some are not. (??)

 

[matthewlouis]

Well phooey, I had the number in wrong spot, it works, sorry to bother you.