Dynamic Reference inside Rank.EQ formula

[Nomarch]

Hello, this is a bit of a new concept (for me) I'm trying to master here, so any help or pointing in the right direction is appreciated.

I have a series of numbers (anywhere from 100 to 1000 unique numbers in the series) and I need to rank those numbers according to a trailing number of observations.

For example, I'd like to know the rank of the last value in my series, based on the last 250 observations, and then also have the ability switch that formula to rank the last number based on the last 100 observations, or last 500 observations.

Put another way, within the Rank.EQ formula: RANK.EQ(number, ref, [order]), is there a way to dynamically change the 'ref' portion of the formula to accommodate various ranges (rank over 250 observations, 500 observations, etc)?

I've looked into nesting INDIRECT and OFFSET, but couldn't figure out how make it work. I'm pretty green in VBA, but ready to try that out if that's the best way to get this done.

 

[Peter_SSs]

For example, I'd like to know the rank of the last value in my series, based on the last 250 observations, and then also have the ability switch that formula to rank the last number based on the last 100 observations, or last 500 observations.

Not sure that I have understood your requirement, and don't know your layout, but see if this is any use.
I'm assuming no blank cells within the cells containing numbers.

As shown, this ranks the 2 at the bottom of the list 3rd out of the last 4 cells (green).
If we change cell D1 to 6 then it would rank the 2 at the bottom out of the last 6 cells (green & yellow) & that result would be 5.

Excel Workbook
	B	C	D	E
1		13	4	3
2	5
3	5
4	6
5	7
6	4
7	8
8	5
9	9
10	3
11	1
12	5
13	6
14	2
15
16
Rank out of last n

 

[Nomarch]

Hello Peter, thank you for your response, and for investing the time in answering my question. This definitely got me pointed in the right direction. Cheers!

 

[Nomarch]

May I just get a quick explanation of what is going on inside the Lookup formula? I don't understand the 9.99E+307 part of the formula. What is that supposed to exactly?

 

[Peter_SSs]

I don't understand the 9.99E+307 part of the formula. What is that supposed to exactly?

See here