CODEWITHSUNDEEP
exceldynamicrange
RTrain3k
RTrain3k

Reputation: 867

Create dynamic cell block reference in excel formula

I am at a loss as to how to create a dynamic cell block reference in an Excel formula. As a simple example, if I have 5 id's with scores per block and I needed to rank the scores within each block, what would be the easiest way to create formulas to do this? I am aware that you can create the formulas in the first 5 cells using relative references (i.e. =RANK.EQ(B#, B2:B6)), copy those cells, and paste in the range that highlights the 15 cells.

enter image description here

Upvotes: 1

Views: 1225

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

If the blocks are 5 rows every time, then you can create ongoing 5-row-blocks in column B using INDEX the following way:

enter image description here

Formula in helper column F beginning in F2 downwards:

=2+INT((ROW(A1)-1)/5)*5

Formula in helper column G beginning in G2 downwards:

=2+4+INT((ROW(A1)-1)/5)*5

Formula in C2 downwards:

=RANK(B2,INDEX(B:B,F2):INDEX(B:B,G2))

Formula in D2downwards (without helper columns):

=RANK(B2,INDEX(B:B,2+INT((ROW(A1)-1)/5)*5):INDEX(B:B,2+4+INT((ROW(A1)-1)/5)*5))

Upvotes: 1

Related Questions