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.

I've modified the code as I mentioned in a previous reply. If you want to see
it, let me know. It will most likely be lower to calculate than a formula, but
if you decide you want to see data for periods of different lengths, it will
be a lot easier to generate the formula <g.

Hi Frank, Your formulas did work after all. I was able to determine my
data had a couple spots in it the confused the formula to work right. I
repaired my data and your formulas work great. You are a genius when it
comes to this software.
I really appreciate your assistance!
George.

"Frank Kabel" wrote:

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.