Try this formula: =IF(C2="Buy",(B2/INDEX(INDIRECT("B"&ROW()&":B65536"),MATCH(0,INDIRE CT("D"&ROW()&":D65536"),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.
---
frmsrcurl:
http://msgroups.net/microsoft.public...ge-Calculation