Tie Breaker selection

C

calla

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

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
Click to expand...
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.
 
T

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
 
T

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
 
Top