Excel Formula HELP please

[ThePriceIsRight]

I am looking to keep track of my roto league by myself on excel. I\'m not good with computers but I have managed to figure out how to rank teams based on the following formula.

ex: 4 teams ranking PIM category
A B C
1 Team A 100 PIM =RANK(B1,B1:B4,1) = 3
2 Team B 70 PIM =RANK(B2,B1:B4,1) = 4
3 Team C 130 PIM =RANK(B3,B1:B4,1) = 2
4 Team D 200 PIM =RANK(B4,B1:B4,1) = 1

My question is what formula do I use if there is a tie.

Ex:

A B C
1 Team A 100 PIM Forumla ??? = 3.5
2 Team B 100 PIM Forumla ??? = 3.5
3 Team C 130 PIM Forumla ??? = 2
4 Team D 200 PIM Forumla ??? = 1

Formula ????

Thx in advance.

[GoHabsGo]

Well, I just tried it in newest excel(2007) and I\'m not sure what the issue is.
A B C
Team A 100 =RANK(B1,B$1:B$4,0) = 3
Team B 100 =RANK(B2,B$1:B$4,0) = 3
Team C 130 =RANK(B3,B$1:B$4,0) = 2
Team D 200 =RANK(B4,B$1:B$4,0) = 1

A B C
Team A 100 =RANK(B1,B$1:B$4,0) = 3
Team B 70 =RANK(B2,B$1:B$4,0) = 4
Team C 130 =RANK(B3,B$1:B$4,0) = 2
Team D 200 =RANK(B4,B$1:B$4,0) = 1

A B C
Team A 100 =RANK(B1,B$1:B$4,0) = 2
Team B 70 =RANK(B2,B$1:B$4,0) = 4
Team C 100 =RANK(B3,B$1:B$4,0) = 2
Team D 200 =RANK(B4,B$1:B$4,0) = 1

FYI the last parameter is for the order 0 is for decreasing and 1 is for increasing.

Are you having an issue with a decimal showing up? May be you need to use =Trunc(RANK(B4,B$1:B$4,0)) - This will remove the decimal. It does not round, but trims the decimal off.

[ThePriceIsRight]

GoHabsGo wrote:
[quote]Well, I just tried it in newest excel(2007) and I\'m not sure what the issue is.
A B C
Team A 100 =RANK(B1,B$1:B$4,0) = 3
Team B 100 =RANK(B2,B$1:B$4,0) = 3
Team C 130 =RANK(B3,B$1:B$4,0) = 2
Team D 200 =RANK(B4,B$1:B$4,0) = 1
[quote]

GoHabsGo,

I can get the rank you used but what formula to I use to get Team A and B who tied for 3rd to get 3.5 pts each instead of 3. In a roto-league there would be 7 pts available and each team would get 3.5 each instead of 3.

[Chicleteur]

Here\'s a link to Microsoft Knowledge base I\'ve found for you:

http://support.microsoft.com/kb/828678/en-us

I gave it a try on my computer and it\'s working

Hope this help!

[lanky522]

lol... that looks like a better solution than what my recommendation would have been...

=IF(AND($G$51=$G$52,$G$51=$G$53,$G$51=$G$54),G51+0 .25,
IF(AND($G$51=$G$52,$G$51=$G$53),G51+0.33,IF(AND
($G$51=$G$52,$G$51=$G$54),G51+0.33,IF(AND
($G$51=$G$53,$G$51=$G$54),G51+0.33,IF($G$51=$G$52, G51+0.5,IF
($G$51=$G$53,G51+0.5,IF($G$51=$G$54,G51+0.5,G51))) ))))

Mine\'s kind of complicated... :blush: Just goes to show that there are multiple ways (good and bad) to make things in excel work...

[Chicleteur]

lanky522 wrote:

lol... that looks like a better solution than what my recommendation would have been...

=IF(AND($G$51=$G$52,$G$51=$G$53,$G$51=$G$54),G51+0 .25,IF(AND($G$51=$G$52,$G$51=$G$53),G51+0.33,IF(AN D($G$51=$G$52,$G$51=$G$54),G51+0.33,IF(AND($G$51=$ G$53,$G$51=$G$54),G51+0.33,IF($G$51=$G$52,G51+0.5, IF($G$51=$G$53,G51+0.5,IF($G$51=$G$54,G51+0.5,G51) ))))))

Well sorry to tell you this lanky, but I think so!

[lanky522]

Chicleteur wrote:

lanky522 wrote:

lol... that looks like a better solution than what my recommendation would have been...

=IF(AND($G$51=$G$52,$G$51=$G$53,$G$51=$G$54),G51+0 .25,IF(AND($G$51=$G$52,$G$51=$G$53),G51+0.33,IF(AN D($G$51=$G$52,$G$51=$G$54),G51+0.33,IF(AND($G$51=$ G$53,$G$51=$G$54),G51+0.33,IF($G$51=$G$52,G51+0.5, IF($G$51=$G$53,G51+0.5,IF($G$51=$G$54,G51+0.5,G51) ))))))

Well sorry to tell you this lanky, but I think so!

Yeah... I\'m kind of a self tought excel user... :blink:

+2 Karma for that site that you just linked though. It will help me a LOT (with all sorts of different functions), because while I can pretty much always figure out a way to make stuff work, I constantly find myself thinking, \"there\'s gotta be a better way to do this...\"

[Crazynuck]

Thx alot Chic,

I\'m too lazy to log off and log in with thepriceisright

I had the right formula all along but I didn\'t insert o new column. I thought I could do it with one. It works now :woohoo:


You\'ll get shot by the Karma slampa machina gun once it reloads.

[Chicleteur]

NP guys! Yeah, I found how to do it in the help, but like you was trying to do it all at once.

[Crazynuck]

yeah i found the correct formaula but tried to fit it into one. I\'ll just hide the other column. finished