Function that identifies cell in a range that is X lower than the previous max?

[hadamhiram]

Howdy Excel Geniuses!

I'm trying to create a function (UDF) that looks at the next Y rows of data (a 1-dimensional array, let's call it 15 rows), and identifies the first place where the next row tested is more than X amount less than whatever the previous maximum value in the array was.

So if X=3, and our array is: 10,9,8,9,11,12,11,13,12,10,9,10,8,7,6 ...

The 10 in bold is the first cell where the next value is 3 points lower than the previous high of 13.


I originally wanted to perform this using a formula comprised of Excel's own functions, but I haven't been able to figure it out!

I'm a complete novice with VBA and haven't been able to get a proper For ... Next loop working...

Oh, and if it makes any difference: Y and X are variables that are stored at the top of the spreadsheet. I'm not sure whether the VBA function should create new variables for them, or just use Range.() ...

Can anyone help?

Thanks!

[protonLeah]

heres a formula solution. The calculation columns can be hidden.

[hadamhiram]

That's very helpful, thanks!