play around with MATCH and OFFSET to get your desired result.
=OFFSET(I1,MATCH("sell",I2:I22,0),1)-OFFSET(I1,MATCH("buy",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
[email protected]
"kman" <[email protected]> wrote in message
news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...fo&userid=3337
> View this thread: http://www.excelforum.com/showthread...hreadid=495931
>