Specifically, I'm hoping to create formula for a trailing stop. If the starting value in A1 is 100 and there are hundreds of rows of data, then the formula (in B1) would look down through those rows sequentially. If the value 95 (initial threshold) appears before the value 110, then the formula returns 95 in B1. BUT, if the the value 110 appears then the initial threshold is reset to a higher value (say, 105), and so now the formula continues down the rows but it won't stop until it hits a cell with 105. If the value in the cells rises to 111, then the threshold is raised to 106, etc, so that the "stop" from then on always trails 5 points behind the max.

I could do this in VBA, but it would be slow because I'd like to have these formulas in every cell in the B column and I have tens of thousands of rows of data.

Thanks in advance for your help!