Ranking fantasy footballplayers

[LarsLanghalm]

I have a huge sheet with a lot of football players and their stats
summed up in on number. Now I would like to rank them, I have found the
"=RANK(U3,U$3:U$225)" formula, but could I alså generate a list of the
players with names and their relative position next to each other, and
number one at the top and so on?

Excample:

----T--------------U---------V----------W------------------------X
1--NAME---------VALUE----RANK-----PLAYER RANKING-----RANK
2--Ronaldo ------ 34--------2---------Ronaldinho-------------1
3--Beckham ---- 12--------4----------Ronaldo----------------2
4--Ronaldinho--- 36--------1----------Ballack-----------------3
5--Ballack--------20--------3----------Beckham---------------4


How do I generate columns W and X from the data and the names in T, U
and V?

(And if anyone should know, how do I make my danish Excel version
understand an english formula?)

 

[Per Sjoblom]

Here's from an answer by Dave Peterson

"start a new worksheet or workbook(just to be safe)

Have him hit alt-f11 to get to the VBE (where macros live)
then hit ctrl-g to see the immediate window.

Have him type this and hit enter

range("a1").formula = "=vlookup(b1,c:d,2,false)"

And hit enter.

Then back to excel and have him read you the formula in A1 of the
activesheet. "

replace the formula with whatever formula you are using


I am not sure I understand what you are trying to do, however it looks like
rather a big task, here's a link to ranking

http://www.cpearson.com/excel/rank.htm



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)




"LarsLanghalm" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

Hi!

I'm assuming there are duplicate ranks which makes it a little more
complicated.

Enter this formula (after you get it translated!) in W2. This is an array
formula and has to be entered using the key combination of
CTRL,SHIFT,ENTER.....not just ENTER:

=INDEX(T$2:T$10,SMALL(IF(V$2:V$10=X2,ROW(T$2:T$10)-ROW(T$2)+1),COUNTIF(X$2:X2,X2)))

Enter this formula (after youget it translated!) in X2:

=SMALL(V$2:V$10,ROWS($1:1))

Select both cells, W2 and X2 then copy down as needed.

Adjust the ranges to suit.

Biff

 

[LarsLanghalm]

Thanks for the answers, Im going to have a close look at it tomorrow
Just want to clear one thing up:

Biff:I'm assuming there are duplicate ranks which makes it a littl
more
complicated.

I'm sorry... I understand why you thought that, the U column is no
relevant for you guys (the V column is just based on the U column, bu
I just want to make a 'simple' rank based on V, but with the nam
attached to the rank...)
My first example:

----T--------------U---------V----------W------------------------X
1--NAME---------VALUE----RANK-----PLAYER RANKING-----RANK
2--Ronaldo ------ 34--------2---------Ronaldinho-------------1
3--Beckham ---- 12--------4----------Ronaldo----------------2
4--Ronaldinho--- 36--------1----------Ballack-----------------3
5--Ballack--------20--------3----------Beckham---------------4

So the simpler version is this:

----T----------(U)--------V------------W------------------------X
1--NAME---------------RANK-------PLAYER RANKING-----RANK
2--Ronaldo --------------2-----------Ronaldinho-------------1
3--Beckham -------------4------------Ronaldo----------------2
4--Ronaldinho------------1------------Ballack-----------------3
5--Ballack----------------3------------Beckham---------------4

How do I generate columns W and X from the data and the names in T an
V

 

[Biff]

Hi!

My formula doesn't depend on column U. It depends on the actual rank from
column V. If there are duplicate ranks, my formula as is accounts for that.
If there are no duplicate ranks (and NEVER will be) then it's a lot simpler.

Formula for column W:

=INDEX(T$2:T$10,SMALL(V$2:V$10,ROWS($1:1)))

You can still use this formula in column X:

=SMALL(V$2:V$10,ROWS($1:1))

Biff

"LarsLanghalm" <[email protected]>
wrote in message

 

[Biff]

Ooops!

I goofed......

Formula for column W:
=INDEX(T$2:T$10,SMALL(V$2:V$10,ROWS($1:1)))

Should be:

=INDEX(T$2:T$10,MATCH(SMALL(V$2:V$10,ROWS($1:1)),V$2:V$10,0))

Biff

 

[KL]

Hi Lars,

I have a huge sheet with a lot of football players and their stats
summed up in on number. Now I would like to rank them, I have found the
"=RANK(U3,U$3:U$225)" formula, but could I alså generate a list of the
players with names and their relative position next to each other, and
number one at the top and so on?
How do I generate columns W and X from the data and the names in T, U
and V?

Have a look here: http://www.cpearson.com/excel/rank.htm
There is also a sample file for download there: http://www.cpearson.com/Zips/RANK.ZIP

(And if anyone should know, how do I make my danish Excel version
understand an english formula?)

Try downloading and installing the add-in called TranslateIT from the
following page: http://members.chello.nl/jvolk/keepitcool/download.html

 

[LarsLanghalm]

Thanks everyone... I am new to Excel and just trying to learn by doing.
But couldn't figure this one out on my own.

Will start trying out your ideas and implement.

 

[LarsLanghalm]

Thanks for the help. I made it work and got a lot of new ideas from all
the formulas.

But there are two basic things I don't get:

1: How do I avoid moving the edges and the background color in my
schemes/schedules/charts (what du you call an area with a number of
columns and with a colored frame and maybe with certain ereas with
colored background?).


2: How do I create a calculater? I would like to be able to add sets of
data daily to and have the 'calculater' add add it to the former data
and correct a constant average (finding the average is easy, but making
the sheet constantly add when I paste new data, instead of just changing
the data, is my problem).


If anyone knows and feels like explaining. Thanks...