Is there a way to dynamically change the range used for a calculation in MS Excel?

Question

I have a dataset like this -

slice	time_id	weight
1	0	10
2	0	20
3	0	30
1	1	10
2	1	5
3	1	25
1	2	30
2	2	10
3	2	20

What will be the best method to get a rank column, which computes the rank for slices corresponding to the previous time_id? For instance, the output for the same table should look something like this -

slice	time_id	weight	Rank
1	0	10	0
2	0	20	0
3	0	30	0
1	1	10	3
2	1	5	2
3	1	25	1
1	2	30	2
2	2	10	3
3	2	20	1

I tried the excel rank function (had to manually set the rank range in the function, rank(slice, range) for every time_id), but it is too much manual work and I have slices and time periods in the hundreds. So what will be the best way to achieve this?

My objective is to find the top n slices for a time_id, based on weight during time_id-1

Answer 1

Got the solution from Microsoft support and it works-

Put this formula in E2 and drag down =IF($B2=0,0,COUNTIFS($B$2:$B$100,INDEX($B$2:$B$100,ROW(2:2)-4),$C$2:$C$100,">"&INDEX($C$2:$C$100,ROW(2:2)-4))+1)