Hi,
I have a range of stock prices. with indication for buy or sell. and i
want 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
and so on....


--
kman
------------------------------------------------------------------------
kman's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread...hreadid=495926

See my answer in your OTHER post. Not necessary or desirable to post in more
than one group.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"kman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi,
> I have a range of stock prices. with indication for buy or sell. and i
> want 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
> and so on....
>
>
> --
> kman
> ------------------------------------------------------------------------
> kman's Profile:
> http://www.excelforum.com/member.php...fo&userid=3337
> View this thread: http://www.excelforum.com/showthread...hreadid=495926
>

Another response to try out, as posted just
to your earlier post in .worksheet.functions
(as Don has advised, please do not multi-post ..)

------
Another play to try ..

A sample construct is available at:
http://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="","",COUNTIF($A$1:A1,A1))
Put in C2: =IF(A2="","",IF(A2=A1,"",COUNTIF($A$1:A2,A2)))

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

Put in E2, array-enter the formula (i.e. press CTRL+SHIFT+ENTER):
=IF(A2="","",IF(A2=A1,"",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
http://savefile.com/projects/236895
--
"kman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi,
> I have a range of stock prices. with indication for buy or sell. and i
> want 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
> and so on....
>
>
> --
> kman
> ------------------------------------------------------------------------
> kman's Profile:
http://www.excelforum.com/member.php...fo&userid=3337
> View this thread: http://www.excelforum.com/showthread...hreadid=495926
>

> Put in C1: =IF(A1="","",COUNTIF($A$1:A1,A1))
Formula above is a little superfluous (clean-up overlooked earlier, sorry)
Simply put in C1: =IF(A1="","",1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--