# Ranking a column of averages from 1st to 24th

#### Rory2010

##### New Member
Hello,

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!

Rory

#### kchiba

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

Cheers

kanti

#### Rory2010

##### 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

=LARGE(ROUNDDOWN('2010'!\$D\$3:\$D\$5+\$D\$8:\$D\$13,0),\$A14:\$A37)

=LARGE(ROUNDDOWN('2010'!\$D\$3:\$D\$5+'2010'!\$D\$8:\$D\$13,0),\$A14:\$A37)

=LARGE(ROUNDDOWN(('2010'!\$D\$3:\$D\$5)+('2010'!\$D\$8:\$D\$13),0),\$A14:\$A37)

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

Any help would be appreciated!

Rory

#### xld

##### Member
Why not just use

=LARGE(ROUNDDOWN('2010'!\$D\$3:\$D\$5+'2010'!\$D\$8:\$D\$13,0),ROW(A1))

and copy down

#### Rory2010

##### 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:

=LARGE(ROUNDDOWN('2010'!\$D\$3:\$D\$5+'2010'!\$D\$8:\$D\$13,0),ROW(A1))

=LARGE(ROUNDDOWN('2010'!\$D\$3:\$D\$5+'2010'!\$D\$8:\$D\$13,0),ROW(A1:A9))

=LARGE(ROUNDDOWN('2010'!\$D\$3:\$D\$5+'2010'!\$D\$8:\$D\$13,0),ROW(A1:A24))

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

It doesn't read it correctly.

#### Hui

##### Excel Ninja
Staff member
Rory

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

For guides on where and how to post files read:

http://chandoo.org/forums/topic/posting-a-sample-workbook

#### Rory2010

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

www.ilovebodyjewelry.com/prodimages/FantasyFootball-2010.xls

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

Rory

#### Rory2010

##### 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!

Rory

#### Hui

##### Excel Ninja
Staff member
Rory

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

#### Rory2010

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

Rory

#### Hui

##### Excel Ninja
Staff member
Press F9 to recalculate

#### Rory2010

##### New Member
No that didn't help, plus I have the #DIV/0! error in the 2010!D3 row with the blank cells

#### Rory2010

##### New Member
plus that put #### in the 2010! AM column as well

#### Hui

##### Excel Ninja
Staff member
Make the AM column wider

#### Rory2010

##### 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?

http://www.ilovebodyjewelry.com/prodimages/FantasyFootball-2010-2ndEdition.xls

#### Hui

##### 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

#### Rory2010

##### 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)

to

=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!!

Rory