software

Hi,
I have a range of stock prices. with indication for buy or sell. and i
want a function to calculate the return of the range for example :

buy 522.73
buy 527.9
buy 528.81
buy 532.33
buy 535.1
buy 536.23
buy 537.03
buy 538.57
sell 537.54
sell 532.86
sell 538.14
sell 533.08
buy 531.22

I want to calculate the return for the first buy signal. the range is
from the start to the first sell signal 537.54- 522.73
then the sell range untill the first buy signal 531.22- 537.54--
kman
------------------------------------------------------------------------
kman's Profile: www.excelforum.com/member.php...foamp;userid=3337
View this thread: www.excelforum.com/showthread...hreadid=495931play around with MATCH and OFFSET to get your desired result.
=OFFSET(I1,MATCH(quot;sellquot;,I2:I22,0),1)-OFFSET(I1,MATCH(quot;buyquot;,I2:I22,0),1)
This gives 14.81

You can use the same idea for the next one.
You could also use match within an INDEX formula
--
Don Guillett
SalesAid Software

quot;kmanquot; gt; wrote in message
...
gt;
gt; Hi,
gt; I have a range of stock prices. with indication for buy or sell. and i
gt; want a function to calculate the return of the range for example :
gt;
gt; buy 522.73
gt; buy 527.9
gt; buy 528.81
gt; buy 532.33
gt; buy 535.1
gt; buy 536.23
gt; buy 537.03
gt; buy 538.57
gt; sell 537.54
gt; sell 532.86
gt; sell 538.14
gt; sell 533.08
gt; buy 531.22
gt;
gt; I want to calculate the return for the first buy signal. the range is
gt; from the start to the first sell signal 537.54- 522.73
gt; then the sell range untill the first buy signal 531.22- 537.54
gt;
gt;
gt; --
gt; kman
gt; ------------------------------------------------------------------------
gt; kman's Profile:
gt; www.excelforum.com/member.php...foamp;userid=3337
gt; View this thread: www.excelforum.com/showthread...hreadid=495931
gt;

If I understand correctly I should do it manualy the first range, the
second range ... I am looking fo a function that will do it automaticly--
kman
------------------------------------------------------------------------
kman's Profile: www.excelforum.com/member.php...foamp;userid=3337
View this thread: www.excelforum.com/showthread...hreadid=495931Is this automatic enough?
Merry Xmas

Sub buysell()
x = Range(quot;i2:i22quot;).Find(quot;buyquot;).Address
y = Range(quot;i2:i22quot;).Find(quot;sellquot;).Address
z = Range(y amp; quot;:i22quot;).Find(quot;buyquot;).Address
aa = Format(Range(y).Offset(, 1) - _
Range(x).Offset(, 1), quot;$#.00quot;)
MsgBox aa
bb = Format(Range(z).Offset(, 1) - _
Range(y).Offset(, 1), quot;$#.00quot;)
MsgBox bb
End Sub

--
Don Guillett
SalesAid Software

quot;kmanquot; gt; wrote in message
...
gt;
gt; If I understand correctly I should do it manualy the first range, the
gt; second range ... I am looking fo a function that will do it automaticly
gt;
gt;
gt; --
gt; kman
gt; ------------------------------------------------------------------------
gt; kman's Profile:
gt; www.excelforum.com/member.php...foamp;userid=3337
gt; View this thread: www.excelforum.com/showthread...hreadid=495931
gt;
gt; ... I am looking for a function that will do it automatically

Another play to try ..

A sample construct is available at:
www.savefile.com/files/9864468
Calculating return in a range_kman_wks.xls

Assuming the data as posted is in cols A and B, row1 down
Put in C1: =IF(A1=quot;quot;,quot;quot;,COUNTIF($A$1:A1,A1))
Put in C2: =IF(A2=quot;quot;,quot;quot;,IF(A2=A1,quot;quot;,COUNTIF($A$1:A2,A2)))

Put in D1: =IF(C1=quot;quot;,quot;quot;,A1)
Copy D1 down to D2

Put in E2, array-enter the formula (i.e. press CTRL SHIFT ENTER):
=IF(A2=quot;quot;,quot;quot;,IF(A2=A1,quot;quot;,B2-INDEX(B:B,MATCH(1,($C$1:C2=MAX(IF($D$12=A1,$C$
1:C2)))*($A$1:A2=A1),0))))

Select C2:E2, copy down as far as required
Col E should return the desired results
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
gt; Put in C1: =IF(A1=quot;quot;,quot;quot;,COUNTIF($A$1:A1,A1))
Formula above is a little superfluous (clean-up overlooked earlier, sorry)
Simply put in C1: =IF(A1=quot;quot;,quot;quot;,1)
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--