• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ranking a column of averages from 1st to 24th


New Member

I'm pretty much a rookie at excel but learn quickly.

To be completely forward I am making excel spreadsheets for my fantasy football league

and we want something we can track, organize and have readily available for viewing each week. Plus to be able to have all those stats going into each new year.

So I am averaging the scores each week for each player in the list.

I am seperating by one row, each sub group (QB,RB,WR,TE,K,Def)

I have a column of averages that I want to take from one page and display on another as a "TOP 24 PERFORMERS" averages from 1st to 24th.

I've been playing with AVERAGE,LARGE AND RANK for hours and want to see if I can get a hand in the correct direction.

All help would be greatly appreciated!



Active Member
Hi Rory,

Have you tried to SORT the column of averages, this would give you the data in the sequence that you want it.




New Member
Well I needed a formula for the table I want to use on the "Top Performers" worksheet.

I have got this to work =LARGE(ROUNDDOWN('2010'!$D$3:$D$5,0),$A14:$A37)

"2010" is the sheet I'm pulling from

$D$3:$D$5 is the 1st group/array

$A14:$A37 is the list from #1-24

My problem is I can't get it to combine my groups/arrays

I've tried to add a second array but these won't work




Do I need to use a SUM or some other function to combine the arrays?

Any help would be appreciated!



Why not just use


and copy down


New Member
I tried that and I get a #N/A and "A value is not available to the formula or function"

All cells in the formula have values in them???

I tried these and all the same:




I still think it's something to do with combining more than 1 array.

It doesn't read it correctly.


Excel Ninja
Staff member

can you post your file somewhere so we can have a look at it

For guides on where and how to post files read:



New Member
I just stuck it on my website for easy access


The "Top Performers-Test" page is where I have the formula.



New Member
Also since your going to take a look at it my next question would be, how could I get the "Player", "Pos" and "Team" to follow in the same row?

Thanks a Ton!



Excel Ninja
Staff member

add the following equations

2010!AM3: =RANK(D3,$D$3:$D$37,0)

copy down to AM36

2010!D3: =AVERAGE(E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3,AE3,AG3,AI3)+ROW()/10000

copy down to D36

'Top Performers'!B19 =OFFSET('2010'!$A$2,MATCH('Top Performers'!$A19,'2010'!$AM$3:$AM$36,0),COLUMN()-1)

copy down and across to E42


New Member
I tried those and I get #N/A accross the whole Top Performers table

AM3-AM36 cells show "2" in all of them.



New Member
I went back through multiple times from scratch and now I just keep ending up with

#DIV/0! error in the 2010!D3 row with the blank cells

and the AM column as well, with #N/A

Here's what I have maybe I did something wrong?



Excel Ninja
Staff member
You/we made 2 mistakes

1. Delete the contents of the cells in Column B with the ######'s, ie: the cells between each group

2. You entered the formula on Top Performers page as an Array Formula

You will have to select the whole area B19:E42 and press delete and then re-enter

B19: =OFFSET('2010'!$A$2,MATCH('Top Performers'!$A19,'2010'!$AM$3:$AM$36,0),COLUMN()-1)

enter it as a normal formula not an Array Formula

Turn calculation to Automatic


New Member
You are awesome!!!!!

Thank you sooo much!!!

Works Perfectly!

Just had to change =OFFSET('2010'!$A$2,MATCH('Top Performers'!$A19,'2010'!$AM$3:$AM$36,0),COLUMN()-1)


=OFFSET('2010'!$A$2,MATCH('Top Performers'!$A19,'2010'!$AM$3:$AM$36,0),COLUMN()-2)

to get the players names in and move it over one more column.

I can't thank you enough!! :)