Tie Breaker selection

[Guest]

In an office football pool, if everyone has selected a number (total score of Monday night) for the tie-breaker, what formula will work that will select the number closest to the actual total score, plus or minus.

 

[Nick]

Hello

an easy way to do this is in one column use the =ABS
function to see who has the closest absolute value away
from score, then in another column use the =RANK function
and rank with the following syntax =RANK(cell, range, 1)

the person ranked first will be closest to the actual
score.

note, you cannot embed these formulas in one cell as it
creates a circular reference.

Nick

-----Original Message-----
In an office football pool, if everyone has selected a

number (total score of Monday night) for the tie-breaker,
what formula will work that will select the number closest
to the actual total score, plus or minus.

 

[The Poolshark]

I have one along the same lines but I am dealing with a Pool League. W
score by rounds won and I can rank the teams by that fine. I would lik
to be able to use the total points scored to be the tie breaker if
teams have the same rounds won. Example

Team Name Rounds Points
Place
Poolsharks 32 1389
1
Underdogs 28 1401
2
White Tigers 28 1406
2

How do I get it to rank the White Tigers in 2nd and Underdogs in 3rd

 

[Dave Peterson]

Just sort with a primary key of Points and a secondary key of Rounds?????

 

[The Poolshark]

Thank you for the response. here is what I ended up doing.

=SUM(J43,L43/10000) in a separate column.

Then I ranked them like this.

=RANK(Q$43,Q$43:Q$49)+COUNTIF(Q$43:Q43,Q43)-1

It worked perfect.

I got this info from the link below.
http://www.cpearson.com/excel/rank.ht