I misread your question. I couldn't imagine that a trend of a particular
length would be significiant, but then I'm not a technician.

If you can't get the proposed worksheet formula for D500 to work, it would be
possible to modify the VBA code to specify both the data range and the length
of the trend.

That might be easier than reworking those formulas if you decide you now want
to look for 2-day or 4-day trends <g.


On Tue, 26 Oct 2004 20:47:01 -0700, George B.
wrote:

Hello Myrna,
I appreciate your advice. Just to clarify the stock data I am using, "A"
column contains the "low" of day, and the "B" column is a "moving average"
value. I don't care about intraday open, high or close values.

My intention was to identify this "trend" lasting EXACTLY 3 days in this
case. My goal in creating this post was to have a formula in 1 cell at
bottom showing me how many times this "specific trend" occured. Then another
formula in a 2nd cell at bottom telling me what the average % of decline from
the first "low of day" to the 3rd "low of day". (average of all trend
declines together).

As for making a million bucks in the stock market with this, I'm not sure
how any one approach could be THE formula for success. The challenge of
trying to make sense of the market is the driving factor me as much as trying
to make money from it.

I appreciate your help very much.
George.

"Myrna Larson" wrote:

I'm still not sure I understand the problem definition.

In looking at the first formula below, it looks for this pattern like this:
A1<B1, A2<A1, A3<A2, and A4A3. In other words, a "trend" must last EXACTLY
3
days (A1:A3 in this case). Was that your intention? I assumed the trend
could
be of any length. If I was wrong, then the macro won't do.


On Tue, 26 Oct 2004 18:47:03 -0700, George B.
wrote:

Hi, The result in C500 is 28. Result in D500 is #DIV/0!

The formula I used in C500 was exactly the formula you suggested.
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400A3:A399))

The formula I used in D500 was exactly the other formula you suggested
also.
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400A3:A399),(1-A3:A399/A1:A397))/SUMPRODUCT(--A1:A397<B1:B397
),--(A2:A398<A1:A397),--(A3:A399<A2:A398),--(A4:A400A3:A399))

Thanks,
George



"Frank Kabel" wrote:

Hi
what result do you have in C500 and what are the exact formulas you
have used in both cells

--
Regards
Frank Kabel
Frankfurt, Germany

"Frank Kabel" schrieb im Newsbeitrag
...
Hi
not fully sure but try:
C500:

=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400A3:A399))


D500:

=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400A3:A399),(1-A3:A399/A1:A397))/SUMPRODUCT(--(A1:A397<B1:B397
),--(A2:A398<A1:A397),--(A3:A399<A2:A398),--(A4:A400A3:A399))


"George B." wrote:

Hello,
I have a tricky problem I would like to see if anyone is able to
figure out.
I need to find a certain “condition” that occurs many times
through 400 rows
of data. I want to place 2 formulas in cells C500 & D500 that will
find and
calculate the below example:

A B What I need
1 100 98
2 95 97 ----- A2 dips below B2. I need
this
condition identified first.
3 90 94 ----- A3 is less than A2. Column
B is no
longer relevant.
4 85 91 ----- A4 is less than A3 Column
B is no
longer relevant.
5 89 92 ----- A5 is larger than A4 Column
B is no
longer relevant.
6 numbers in A & B after this do not matter after rows 2 thru
5
“condition” has been found.
7 continue down A & B to find the next “condition” as rows 2
thru 5.


Now in C500 instead of a result of “TRUE”, could C500 contain the
total
number of times this “condition” occurred between rows 1 thru 400?
(the
values in columns A & B constantly change).

Finally in cell D500, what formula can I place here to (per example
above)
calculate the % decline in value of A4 from A2? With this
knowledge, I need
all the % declines of all the “conditions” found to result into an
AVERAGE %
drop showing up in cell D500.

Thank you very much to anyone who can help me!
George B.