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?

 

[number kruncher]

Named Ranges are useful here try: FLexibleRange =offset(DR$3,,,counta(DR:DR),1)

Then refer to this FlexibleRange in your formula.

 

[helpneeded2]

Named Ranges are useful here try: FLexibleRange =offset(DR$3,,,counta(DR:DR),1)

Then refer to this FlexibleRange in your formula.

Unfortunately I really couldn't use named ranges. I have over 15 tabs (1 tab for each of the 15 different periods), each containing about 15 different ranking formulas. That would mean 165 different named ranges?

 

[Fluff]

Why not just use

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

and change the 1000 to a row below your data.

 

[number kruncher]

There is no real limit to the number of Named Ranges you can have.

If you change the Scope to the Sheet, rather than the Workbook (default) , then you could use the same Names for each Sheet/Column if this helps?

 

[helpneeded2]

Why not just use

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

and change the 1000 to a row below your data

Some sheets could have up to 10,000 rows of data (and will likely grow to 20,000. I am trying to make the sheets dynamic so that the end rows from all the formulas never have to be changed, and also am trying to save on sheet resources. Even though many of those rows will be blank, wouldn't this still be hard on the system resources to have 15 columns ranking data over 10,000 rows, even though there might only be 1,500 rows of data on some sheets? (Or would it be very negligible?) Right now the sheet runs super slow, and everytime I hit enter it needs 30 to 45 seconds to recalculate the results.

 

[helpneeded2]

There is no real limit to the number of Named Ranges you can have.

If you change the Scope to the Sheet, rather than the Workbook (default) , then you could use the same Names for each Sheet/Column if this helps?

What I meant is that I'm not going to manually name 165 ranges. However, it sounds like I can in theory just name 15 ranges, but not make them associated with a specific sheet -- I have never done that before so I will Google how to do that. Thank you.

 

[Fluff]

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.

 

[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.

Thank you, good to know. 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.

 

[number kruncher]

What I meant is that I'm not going to manually name 165 ranges. However, it sounds like I can in theory just name 15 ranges, but not make them associated with a specific sheet -- I have never done that before so I will Google how to do that. Thank you.

Glad to have helped.