Dynamic ranges in array formulas.

This stems from a suggestion left on a thread I started earlier today. Since it was a separate issue, I figured I should start a new thread. Here's a link to the old thread. I'm trying to use dynamic ranges in an array formula. Or at least that's what I think I'm doing.

http://www.excelforum.com/excel-form...of-values.html

Use dynamic ranges or refer to a range that is bigger than you think you'll need but less than the entire column.

I'm unfamiliar with dynamic ranges, but after searching online I tried what I thought was suggested but ran into errors. I was able to use it with simple averageif formulas, but not the array formula provided in the previous thread. I had a similar issue in the last thread because of some #N/A values in the column being searched against. I had some #VALUE cells in the columns involved this time, but got rid of those and that didn't fix the issue. I used the following formula to name different ranges and tweaked it accordingly.

=OFFSET(Results!$L$1,0,0,COUNTA(Results!$L:$L),1)

However, when I try substituting the named ranges into the array formula given in the previous thread, I'm back to getting #N/A as a result. This formula below is entered by pressing CTRL+SHIFT+ENTER.

=AVERAGE(IF(ROW(Player_ID)>=LARGE(IF(Player_ID=BF2,ROW(Player_ID)),10),IF(Player_ID=BF2,Points)))

The averageif formula below works without issue.

=AVERAGEIF(Player_ID,BF2,Points)

My spreadsheet works without using dynamic ranges, but since it is so large I imagine it will help it bog down less. Any help would be appreciated. Thanks!

Noting your comments about 40 minute recalculation in your other thread, I would suggest avoiding volatile functions where possible.

I would guess that your named ranges have an inconsistent number of rows, to make the ranges consistent in size, first pick 1 column of your data set that you can use to find the last row of data, then create a named range to find it, for example

Name:= LastRow

Refers to: =MATCH(1e+100,$L:$L)

Which is to find the last row in a column of numeric data, if you have text data, use this formula instead.

Refers to: =MATCH("zzzz",$L:$L)

Then generate the named ranges to use in your array formula using

=$L$1:INDEX($L:$L,LastRow)

=$A$1:INDEX($A:$A,LastRow)

etc. Using this method, the last row is always defined by column L, so you won't get inconsistency / errors resulting from blanks / empty cells, or false counts caused by, for example, headers / subtotals in some, but not all columns.