How to reference a column of data where the end row is variable?

[helpneeded2]

I have several ranking formulas on my sheet. The data is not in a table, and cannot be. I want to the rank reference to start at row 3, and go until it finds the end of the data. My current formula is this:

=IF(A3="","",PERCENTRANK.INC(DR:DR,DR3))

I have tried to amend the range to being: DR$3:DR, however then I end up with an invalid name error.

The reason I am not doing DR:DR is because row 1 has a numeric label, and row 2 has a text header. While the RANK formulas do seem to ignore text values like my header in DR:DR, they are still ranking my data in row 1, which I do not want.

My sheet is fairly massive and have several tabs doing this same function across multiple periods of data, so I am look for the least resource intensive way of doing this.

Can someone please suggest a way to omit the first 2 columns of a RANK formula, while having no set end point?

 

[Fluff]

I wasn't sure if DR:DR was just auto stopping at the last row that contains data, or if it is actually looking at every possible row (i.e. 1 million) in a column.

It looks at the entire column, virtually all functions are the same in that regard which is why it's a bad idea to use whole column references.

 

[helpneeded2]

Glad to have helped.

Could you please link me to instruction on how I can make a generic formula on a named range. While I am setting the scope as Workbook, when I enter the formula as a named range, it still adds in a reference to the specific sheet. Even when I try deleting the specific reference, it will just add it back. I cannot find anything on Google that suggests how I can do this.

 

[helpneeded2]

Limiting the formula to 10,000 or 20,000 (or even 50,000) rows will be a lot better than looking at over 1,000,000 rows.

In the suggested fix of using the named range with =offset(DR$3,,,counta(DR:DR),1) then this may not necessarily be saving the calculation time then, since this will be scanning all 1,000,000 rows to tally the rows with data?

 

[number kruncher]

Could you please link me to instruction on how I can make a generic formula on a named range. While I am setting the scope as Workbook, when I enter the formula as a named range, it still adds in a reference to the specific sheet. Even when I try deleting the specific reference, it will just add it back. I cannot find anything on Google that suggests how I can do this.

The named range references ’need’ the sheetname to reference the cells.

A generic format would be;
=OFFSET( [starting point] , offset row , offset column , row count , column count )

 

[Fluff]

That is unlikely to save any time & could make things worse as it's volatile.

 

[helpneeded2]

That is unlikely to save any time & could make things worse as it's volatile.

Thank you. I will just make the end range row a value that is referenced with an indirect, so it will be easier for me to increase the rows range in a year when the data set doubles.

i.e.

=IF(A3="","",PERCENTRANK.INC(DR$3:INDIRECT("DR$"&value_rowEndPeriods),DR3))

 

[number kruncher]

That is unlikely to save any time & could make things worse as it's volatile.

There are a few volatile formulas, however, with care they can still be used to great effect.

 

[Fluff]

I will just make the end range row a value that is referenced with an indirect

That will be far less efficient than the formula you were using as it's volatile & slow.
try it like

=IF(A3="","",PERCENTRANK.INC(DR$3:INDEX(DR:DR,value_rowEndPeriods),DR3))