Scan a series of rows in sequence and identify cell where value falls below previous max?

Howdy Excel Geniuses!

I'm trying to figure out how to go through a series of rows of data (a 1-dimensional array), and spot when the next row tested is more than X amount below whatever the previous maximum value was.

The tricky part is, my data are not smooth - there's noise! So that is why I'm not just looking for the simple MAX in the entire array, and also why I need to see a drop of X amount below the max-so-far in order to flag it.

And my final challenge is that I have to have a column of these formulas, one for each new row: each formula will look through the next 500 rows... and I have tens of thousands of rows. I understand that I should therefore avoid array formulas because they are slow??

I've been banging my head against the desk on this for hours, trying IF and MAX and COUNTIF and MATCH formulas, and I can't get anything to work.

Any ideas?

Is this what you mean?

This is fantastic - and so simple! I can't believe I couldn't figure it out... I think I can make the rest of the steps from here.

Thanks so much!


EDIT: Argh! Close, but not quite! The solution does its work using the entire column, rather the just doing its work in its own cell - and that means it can't be populated downward for each new row!

So, since I already marked this "Solved", I'm going to repost the question...

----------