• 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

    Hui...

  • 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

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
 

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
 
Top