Formula to retrieve next TRUE occurrence from a list! ???

[stockgoblin42]

Hi, I'm studying the stock market, specifically, the frequency of certain candlesticks. Like the doji, where the opening and closing prices are the same. So, I have the data from Yahoo Finance, then an IF formula to easily show me which dates are dojis. The sessions ago is just a +1 counter. Now, here's the tricky part. I want Excel to automatically put in the price from the last occurance of a doji. So it would have to scan down to the next doji and find it. Is this possible? Is Excel that smart? Are we that smart? Also, it would be great to calculate the difference in sessions between the 2 dojis automatically too. SO, how do I get Last Doji column and Sessions Between Column to be automatic? Smarty pants.



Date......... ..Open..High..Low..Close............dojis:............sessions ago:......last doji:.........sessions between
.......................................................=IF(B2=E2,B2,0)... =SUM(G2+1).. (manual entry!).....(manual entry!)
12/16/2010....3.17 3.37 3.11 3.30................0.......................110..............I want these automatic.
12/15/2010....3.00 3.18 3.00 3.17................0.......................111
12/14/2010....3.02 3.03 3.00 3.02............3.02.......................112.................2.72...................6
12/13/2010....2.98 3.01 2.97 3.00................0........................113
12/10/2010....2.91 2.99 2.91 2.98................0........................114
12/9/2010......2.85 2.92 2.80 2.90................0.......................115
12/8/2010......2.79 2.84 2.76 2.82................0.......................116
12/7/2010......2.74 2.88 2.72 2.77................0........................117
12/6/2010......2.72 2.76 2.70 2.72............2.72.......................118.................2.85...................13
12/3/2010......2.73 2.75 2.72 2.74................0........................119
12/2/2010......2.69 2.76 2.67 2.76................0........................120
12/1/2010......2.71 2.79 2.68 2.70................0.........................121
11/30/2010....2.75 2.79 2.64 2.66................0........................122
11/29/2010....2.80 2.91 2.78 2.78................0.........................123
11/26/2010....2.84 2.87 2.80 2.83................0.........................124
11/24/2010....2.82 2.88 2.80 2.87................0.........................125
11/23/2010....2.83 2.87 2.80 2.80................0.........................126
11/22/2010....2.85 2.88 2.83 2.84................0..........................127
11/19/2010....2.88 2.88 2.83 2.85................0.........................128
11/18/2010....2.91 2.94 2.85 2.87................0.........................129
11/17/2010....2.90 2.96 2.86 2.88................0.........................130
11/16/2010....2.85 3.02 2.83 2.85............2.85.........................131..................3.06..............19
11/15/2010....2.93 3.01 2.88 2.89................0..........................132
11/12/2010....2.93 2.94 2.82 2.87................0..........................133
11/11/2010....2.87 2.97 2.75 2.95................0..........................134
11/10/2010....2.76 2.90 2.76 2.89................0..........................135


Any help would be appreciated and then you can proceed to the riddle of the sphinx.

thanks

Morrison

[Haseeb Avarakkan]

Hello,

The layout is difficult to understand. Can you please attache your dummy sheet?

Down, click on Go Advanced, then down click Manage Attachments

[TMS]

Thanks for the feedback ... I'm not in total agreement, as you might imagine, but you're feedback has been gratefully received. Prefer "lovely, as usual".

Good luck with your search for a solution to your question.

I'm not planning to investigate the riddle of the sphinx ... way beyond me.

Regards

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Cutter]

I have done a few for you to show you how it can be done. It involves a helper column (I used column T) which can be hidden.

Adjust the ranges to suit. Consider using Dynamic Named Ranges.

[stockgoblin42]

Thank you Cutter. That's extremely impressive. To put a cap on such outstanding work, is it possible to hide a row if a FALSE condition is met, say in the doji column? Then, I'd only see the positive results.

As always, your help is appreciated.

Morrison

[Cutter]

Select column I. On the Ribbon choose Data - Filter. A small drop-down will appear in cell I1. Click on it and in the window that pops up you'll see a bunch of check marks in a list. Remove the check beside 0 and click OK.

[stockgoblin42]

That's the capper! Wow, what a program and what great help, Cutter. Thank you. Be well.

[Cutter]

You're welcome - don't forget to mark your thread as SOLVED (click on FAQ at top of page for instructions).

And thanks for the "scales tap".