Dynamic Price Change Calculation

[MikeG2010]

Trying to create formula that will automatically calculate the % price change
in the closing price of a stock where the closing price is greater than a
given value. Currently, I manually scroll down column D until I get the
first "Buy" signal and then write the formula (Bx/$B$y-1) and do the
samething everytime my price paramater is met.

Column A=Date, B=Close Price, C="Buy"where close price greater than $75, D=%
P/L.
The % P/L is calculated (most recent close price/first buy value-1).

Date Close $75.00 %
3/10/2010 80.08 Buy 6.55%
3/9/2010 79.76 Buy 6.12%
3/8/2010 80.62 Buy 7.26%
3/5/2010 80.71 Buy 7.38%
3/4/2010 78.84 Buy 4.90%
3/3/2010 79.24 Buy 5.43%
3/2/2010 77.98 Buy 3.75%
3/1/2010 76.45 Buy 1.72%
2/26/2010 75.16 Buy 0.00%
2/25/2010 74.07
2/24/2010 72.68
2/23/2010 73.64
2/22/2010 76.24 Buy 0.40%
2/19/2010 77.16 Buy 1.61%
2/18/2010 76.6 Buy 0.87%
2/17/2010 75.12 Buy -1.08%
2/16/2010 75.94 Buy 0.00%
2/12/2010 73.68
2/11/2010 74.17
2/10/2010 71.03
2/9/2010 71.58
2/8/2010 69.2
2/5/2010 70.23
2/4/2010 66.74
2/3/2010 70.46
2/2/2010 72.51
2/1/2010 71.59
1/29/2010 66.69
1/28/2010 68.82
1/27/2010 71.25
1/26/2010 71.99
1/25/2010 74.6
1/22/2010 74.23
1/21/2010 76.28 Buy -8.60%
1/20/2010 83.52 Buy 0.07%
1/19/2010 84.6 Buy 1.37%
1/15/2010 84.3 Buy 1.01%
1/14/2010 85.08 Buy 1.94%
1/13/2010 86.15 Buy 3.22%
1/12/2010 84.77 Buy 1.57%
1/11/2010 88.09 Buy 5.55%
1/8/2010 88.1 Buy 5.56%
1/7/2010 85.64 Buy 2.61%
1/6/2010 87.31 Buy 4.61%
1/5/2010 83.96 Buy 0.60%
1/4/2010 83.46 Buy 0.00%


Example 3/10/10 close = 80.08, first buy value in series =75.16 on 2/26/10.
(80.08/75.16-1)= 6.546% which is rounded to 6.55%.
3/9/10 =(79.76/75.16-1)=6.12% etc.

Is there anyway this can be done automatically with a formula or code? Any
help would be greatly appreciated.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[dflak]

Try this formula: =IF(C2="Buy",(B2/INDEX(INDIRECT("B"&ROW()&":B65536"),MATCH(0,INDIRECT("D"&ROW()&"65536"),0)))-1,""

What the formula is based on is that the first "BUY" in the series always turns out to be zero. That's what the match performs. I have to slide the match down a row for each new comparison (otherwise it always finds the FIRST zero and not necessarily the first zero in the series). So thats what the indirect and row() functions do

I use index off the same relative range

Anyway, I tried it with the data you provided and it seems to work.