Is specifying a column from a named range more efficient than calculating over a complete column?

Question

Under the hood of Excel do you think if I use named ranges, for say MAX calculations, it will be more efficient e.g.

I have a named range myNmdRang that is A1:Z20000 with no data in the remainder of the sheet. Column A contains some numeric values. The named range might get adjusted in length tomorrow to 500k rows or just 100rows.

Taditionally I do this to find the MAX in column 1:

Max("A:A")

Is the following more efficient and/or takes up less storage? (can this be proved)

Max(index(myNmdRang,0,1))

Answer 1

As I understand it the named range you are planning on using is more efficient than looking at the whole column as you are simply looking at fewer entries.

---

To save time manually updating the field you could do the following:

You could dynamically assign the length of the name range. That is to say its length will automatically adjust based on the number of entries.

Assuming there are no entries and the only thing in column A are numbers, instead of using =A1:Z2000 as your defined range where you create the named range, you could use:

 =offset($A$1,0,0,counta(A:A),26)

The counta(A:A) will look at the number of numeric entries and define the length of your named range.

so instead of using max(A:A) which looks at all of column 1, you could make another named range just for column 1 say MyFirstRange or you could just put the defined range of it in for the max command and I would do something like this:

 define a named range as:
 MyFirstRange = offset($A$1,0,0,counta(A:A),1)

 =Max(MyFirstRange)

 OR if you dont want to name another range

 =max(offset($A$1,0,0,counta(A:A),1))

---

I know this does not speak totally to efficiency of the two methods. It just saves you redefining your named range manually each day. I hope that helps you out.