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

"kman" wrote in message
...

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